﻿using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Text;
using System.Data;
using System.Linq;
using System.Collections;

using ECCIT;
using ECCIT.Data;
using ECCIT.Business.Model;
using ECCIT.Business.DAL;

using OgilvyOne.Model;
using OgilvyOne.SqlServerDAL;
using OgilvyOne.UKNow.Globalization;
using OgilvyOne.UKNow.Utility;
using OgilvyOne.UKNow.Model;
using OgilvyOne.UKNow.IDAL;
using SortOrder = ECCIT.SortOrder;

namespace OgilvyOne.UKNow.DAL
{
    public partial class EventsDAL : StaticGeneratableDAL, IDALEvents
    {
        private const string TBL_NAME                   = "UKNOW_EVENTS";
        private const string COL_LANG_ID                = "COL_LANG_ID";
        private const string COL_EVENTS_ID              = "COL_EVENTS_ID";
        private const string COL_EVENTS_STATE           = "COL_EVENTS_STATE";
        private const string COL_EVENTS_TITLE           = "COL_EVENTS_TITLE";
        private const string COL_EVENTS_SUBTITLE        = "COL_EVENTS_SUBTITLE";
        private const string COL_EVENTS_SPONSOR         = "COL_EVENTS_SPONSOR";
        private const string COL_EVENTS_FORM            = "COL_EVENTS_FORM";
        private const string COL_EVENTS_CITY            = "COL_EVENTS_CITY";
        private const string COL_EVENTS_VENUE           = "COL_EVENTS_VENUE";
        private const string COL_EVENTS_TIME_START      = "COL_EVENTS_TIME_START";
        private const string COL_EVENTS_TIME_END        = "COL_EVENTS_TIME_END";
        private const string COL_EVENTS_DESC            = "COL_EVENTS_DESC";
        private const string COL_EVENTS_DETAIL          = "COL_EVENTS_DETAIL";

        /*修改人：穆凤凤 
         * 修改时间：2012-04-26
         * 修改内容：
         *      新增加字段 活动手机内容 COL_EVENTS_PHONE_DETAIL
         *      有mff注释的为本次修改的代码
         */
        private const string COL_EVENTS_PHONE_DETAIL = "COL_EVENTS_PHONE_DETAIL";//mff
        /*修改人：穆凤凤 
         * 修改时间：2012-04-10
         * 修改内容：
         *      新增加字段 巡回/教育活动 COL_EVENTS_EDU
         *      有mff注释的为本次修改的代码
         */
        private const string COL_EVENTS_EDU             = "COL_EVENTS_EDU";//mff

        private const string COL_EVENTS_IS_HOT          = "COL_EVENTS_HOT";
        private const string COL_EVENTS_IS_RECOMMEND    = "COL_EVENTS_RECO";
        private const string COL_EVENTS_ORDER           = "COL_EVENTS_ORDER";
        private const string COL_EVENTS_HP_TOP          = "COL_EVENTS_HP_TOP";
        private const string COL_EVENTS_COL_TOP         = "COL_EVENTS_COL_TOP";
        private const string COL_EVENTS_COLUMN_ID       = "COL_EVENTS_COLUMN_ID";
        private const string COL_EVENTS_LAYER_ID        = "COL_EVENTS_LAYER_ID";
        private const string COL_EVENTS_TICKET_URL = "COL_EVENTS_TICKET_URL";
        private const string COL_EVENTS_SERIES_MAIN = "COL_EVENTS_SERIES_MAIN";
        private const string COL_EVENTS_SERIES_ID = "COL_EVENTS_SERIES_ID";
        private const string COL_EVENTS_VENUE_IMG = "COL_EVENTS_VENUE_IMG";
        private const string COL_EVENTS_INSERT_TIME     = "COL_EVENTS_INSERT_TIME";
        private const string COL_EVENTS_UPDATE_TIME     = "COL_EVENTS_UPDATE_TIME";
        private const string COL_EVENTS_LONGITUDE = "COL_EVENTS_LONGITUDE";
        private const string COL_EVENTS_LATITUDE = "COL_EVENTS_LATITUDE";

        private const string COL_EVENTS_SERIES_ISSPECIAL = "COL_EVENTS_SERIES_ISSPECIAL";
        private const string COL_EVENTS_ALLOW_UPLOAD = "COL_EVENTS_UPLOAD";
        private const string COL_EVENTS_TIME_INTERVAL = "COL_EVENTS_TIME_INTERVAL";
        private const string COL_EVENTS_TIME_SHOW = "COL_EVENTS_TIME_SHOW";
        private const string COL_EVENTS_INC_HTML = "COL_EVENTS_INC_HTML";

        /*修改人：陈鹏
         * 修改时间：2012-01-01
         * 修改内容：
         *      新增加字段 活动列表图 COL_EVENTS_LB_IMG
         *      有cp注释的为本次修改的代码
         */
        private const string COL_EVENTS_LB_IMG = "COL_EVENTS_LB_IMG"; //cp

         /*修改人：陈鹏 
         * 修改时间：2012-01-01
         * 修改内容：
         *      新增加字段 活动列表图 COL_EVENTS_LB_IMG
         *      有cp注释的为本次修改的代码
         */
        private const string COL_EVENTS_TOP_IMG = "COL_EVENTS_TOP_IMG"; //cp
        /*修改人：陈鹏
        * 修改时间：2012-01-10
        * 修改内容：
        *      新增加字段 活动列模版id COL_EVENTS_TEMPLATEID
        *      有cp注释的为本次修改的代码
        */
        private const string COL_EVENTS_TEMPLATEID = "COL_EVENTS_TEMPLATEID"; //cp

        /*修改人：熊峰军
        * 修改时间：2012-05-03
        * 修改内容：
        *      新增加字段 手机活动标题
        */
        private const string COL_EVNETS_PHONE_TITLE = "COL_EVNETS_PHONE_TITLE";

        /// <summary>
        /// 增加手机活动的举办地点 2012-5-4 李学明
        /// </summary>
        private const string COL_EVENTS_PHONE_VENUE = "COL_EVENTS_PHONE_VENUE";

        public override string TableName { get { return TBL_NAME; } }
        public override Type ModelType { get { return typeof(EventsModel); } }
        public override IField PrimaryKey { get { return ID; } }

        //[PrimaryKey]
        //public IField LanguageID { get { return FieldFactory.Create(ModelType, "LanguageID", ColumnFactory.CreateColumn(COL_LANG_ID, DataType.Int)); } }
        [PrimaryKey]
        public IField ID { get { return FieldFactory.Create(ModelType, "ID", ColumnFactory.CreateColumn(COL_EVENTS_ID, DataType.Int)); } }
        public IField State { get { return FieldFactory.Create(ModelType, "State", ColumnFactory.CreateColumn(COL_EVENTS_STATE, DataType.Int)); } }
        public IField Title { get { return FieldFactory.Create(ModelType, "Title", ColumnFactory.CreateColumn(COL_EVENTS_TITLE, DataType.String, 200)); } }
        public IField SponsorID { get { return FieldFactory.Create(ModelType, "SponsorID", ColumnFactory.CreateColumn(COL_EVENTS_SPONSOR, DataType.Int)); } }
        public IField ArtFormID { get { return FieldFactory.Create(ModelType, "ArtFormID", ColumnFactory.CreateColumn(COL_EVENTS_FORM, DataType.Int)); } }
        public IField City { get { return FieldFactory.Create(ModelType, "City", ColumnFactory.CreateColumn(COL_EVENTS_CITY, DataType.Int)); } }
        public IField Venue { get { return FieldFactory.Create(ModelType, "Venue", ColumnFactory.CreateColumn(COL_EVENTS_VENUE, DataType.String, 500)); } }
        public IField StartTime { get { return FieldFactory.Create(ModelType, "StartTime", ColumnFactory.CreateColumn(COL_EVENTS_TIME_START, DataType.DateTime)); } }
        public IField EndTime { get { return FieldFactory.Create(ModelType, "EndTime", ColumnFactory.CreateColumn(COL_EVENTS_TIME_END, DataType.DateTime)); } }

        public IField ShortDescription { get { return FieldFactory.Create(ModelType, "ShortDescription", ColumnFactory.CreateColumn(COL_EVENTS_DESC, DataType.String, 800)); } }

        public IField PhoneDetail { get { return FieldFactory.Create(ModelType, "PhoneDetail", ColumnFactory.CreateColumn(COL_EVENTS_PHONE_DETAIL, DataType.NText)); } }  //mff

        public IField Detail { get { return FieldFactory.Create(ModelType, "Detail", ColumnFactory.CreateColumn(COL_EVENTS_DETAIL, DataType.NText)); } }
        public IField Education { get { return FieldFactory.Create(ModelType, "Education", ColumnFactory.CreateColumn(COL_EVENTS_EDU, DataType.NText)); } }

        public IField IsHot { get { return FieldFactory.Create(ModelType, "IsHot", ColumnFactory.CreateColumn(COL_EVENTS_IS_HOT, DataType.Bit)); } }
        public IField IsRecommendation { get { return FieldFactory.Create(ModelType, "IsRecommendation", ColumnFactory.CreateColumn(COL_EVENTS_IS_RECOMMEND, DataType.Bit)); } }
        public IField Order { get { return FieldFactory.Create(ModelType, "Order", ColumnFactory.CreateColumn(COL_EVENTS_ORDER, DataType.Int)); } }
        public IField IsHomepageTop { get { return FieldFactory.Create(ModelType, "IsHomepageTop", ColumnFactory.CreateColumn(COL_EVENTS_HP_TOP, DataType.Bit)); } }
        public IField IsColumnTop { get { return FieldFactory.Create(ModelType, "IsColumnTop", ColumnFactory.CreateColumn(COL_EVENTS_COL_TOP, DataType.Bit)); } }
        public IField ColumnID { get { return FieldFactory.Create(ModelType, "ColumnID", ColumnFactory.CreateColumn(COL_EVENTS_COLUMN_ID, DataType.Int)); } }
        public IField LayerIDs { get { return FieldFactory.Create(ModelType, "LayerIDs", ColumnFactory.CreateColumn(COL_EVENTS_LAYER_ID, DataType.String, 100)); } }

        public IField InsertedTime { get { return FieldFactory.Create(ModelType, "InsertedTime", ColumnFactory.CreateColumn(COL_EVENTS_INSERT_TIME, DataType.DateTime)); } }
        public IField UpdatedTime { get { return FieldFactory.Create(ModelType, "UpdatedTime", ColumnFactory.CreateColumn(COL_EVENTS_UPDATE_TIME, DataType.DateTime)); } }
        public IField Lng { get { return FieldFactory.Create(ModelType, "Lng", ColumnFactory.CreateColumn(COL_EVENTS_LONGITUDE, DataType.String,200)); } }
        public IField Lat { get { return FieldFactory.Create(ModelType, "Lat", ColumnFactory.CreateColumn(COL_EVENTS_LATITUDE, DataType.String,200)); } }

        public IField ListImg { get { return FieldFactory.Create(ModelType, "ListImg", ColumnFactory.CreateColumn(COL_EVENTS_LB_IMG, DataType.String, 200)); } } //cp
        
        public IField TopImg { get { return FieldFactory.Create(ModelType, "TopImg", ColumnFactory.CreateColumn(COL_EVENTS_TOP_IMG, DataType.String, 200)); } } //cp

        public IField TemplateID { get { return FieldFactory.Create(ModelType, "TemplateID", ColumnFactory.CreateColumn(COL_EVENTS_TEMPLATEID, DataType.Int)); } }//cp

        public IField SubTitle { get { return FieldFactory.Create(ModelType, "SubTitle", ColumnFactory.CreateColumn(COL_EVENTS_SUBTITLE, DataType.String, 500)); } }
        public IField TicketUrl { get { return FieldFactory.Create(ModelType, "TicketUrl", ColumnFactory.CreateColumn(COL_EVENTS_TICKET_URL, DataType.String, 200)); } }
        public IField VenueImagePath { get { return FieldFactory.Create(ModelType, "VenueImagePath", ColumnFactory.CreateColumn(COL_EVENTS_VENUE_IMG, DataType.String, 200)); } }
        public IField IsSeriesMain { get { return FieldFactory.Create(ModelType, "IsSeriesMain", ColumnFactory.CreateColumn(COL_EVENTS_SERIES_MAIN, DataType.Bit)); } }
        public IField SeriesMainEventsID { get { return FieldFactory.Create(ModelType, "SeriesMainEventsID", ColumnFactory.CreateColumn(COL_EVENTS_SERIES_ID, DataType.Int)); } }
        public IField IsSpecial { get { return FieldFactory.Create(ModelType, "IsSpecial", ColumnFactory.CreateColumn(COL_EVENTS_SERIES_ISSPECIAL, DataType.Int)); } }
        public IField Week { get { return FieldFactory.Create(ModelType, "Week", ColumnFactory.CreateColumn(COL_EVENTS_TIME_INTERVAL, DataType.String,20)); } }
        public IField ShowTime { get { return FieldFactory.Create(ModelType, "ShowTime", ColumnFactory.CreateColumn(COL_EVENTS_TIME_SHOW, DataType.String,200)); } }
        public IField UpLoadHtmlName { get { return FieldFactory.Create(ModelType, "UpLoadHtmlName", ColumnFactory.CreateColumn(COL_EVENTS_INC_HTML, DataType.String,200)); } }
        public IField AllowUploadAttachToComment { get { return FieldFactory.Create(ModelType, "AllowUploadAttachToComment", ColumnFactory.CreateColumn(COL_EVENTS_ALLOW_UPLOAD, DataType.Bit)); } }

        public IField PhoneTitle { get { return FieldFactory.Create(ModelType, "EventPhone", ColumnFactory.CreateColumn(COL_EVNETS_PHONE_TITLE, DataType.NText)); } }
        public IField PhoneVenue { get { return FieldFactory.Create(ModelType, "PhoneVenue", ColumnFactory.CreateColumn(COL_EVENTS_PHONE_VENUE, DataType.String, 500)); } }

        public override IField[] InsertFields { get { return new IField[] { LanguageID, ID, State, Title, SubTitle, SponsorID, ArtFormID, City, Venue, StartTime, EndTime, ShortDescription, PhoneDetail, Detail, Education, IsHot, IsRecommendation, Order, IsHomepageTop, IsColumnTop, ColumnID, LayerIDs, InsertedTime, UpdatedTime, ListImg, TopImg, TemplateID, TicketUrl, VenueImagePath, IsSeriesMain, SeriesMainEventsID, Lng, Lat, Week, ShowTime, UpLoadHtmlName, AllowUploadAttachToComment, PhoneTitle, PhoneVenue }; } }
        public override IField[] SelectFields { get { return new IField[] { LanguageID, ID, State, Title, SubTitle, SponsorID, ArtFormID, City, Venue, StartTime, EndTime, ShortDescription, PhoneDetail, Detail, Education, IsHot, IsRecommendation, Order, IsHomepageTop, IsColumnTop, ColumnID, LayerIDs, InsertedTime, UpdatedTime, ListImg, TopImg, TemplateID, TicketUrl, VenueImagePath, IsSeriesMain, SeriesMainEventsID, IsSpecial, Lat, Lng, Week, ShowTime, UpLoadHtmlName, AllowUploadAttachToComment, SEOMetaDescription, SEOMetaKeywords, SEOTitle, IsHtmlFile, HtmlFileName, PhoneTitle, PhoneVenue }; } }
        public override IField[] UpdateFields { get { return new IField[] { LanguageID, State, Title, SubTitle, SponsorID, ArtFormID, City, Venue, StartTime, EndTime, ShortDescription, PhoneDetail, Detail, Education, IsHot, IsRecommendation, Order, IsHomepageTop, IsColumnTop, ColumnID, LayerIDs, UpdatedTime, ListImg, TopImg, TemplateID, TicketUrl, VenueImagePath, IsSeriesMain, SeriesMainEventsID, Lat, Lng, Week, ShowTime, UpLoadHtmlName, AllowUploadAttachToComment, PhoneTitle, PhoneVenue }; } }

        public EventsDAL() { }

        public EventsDAL(LanguageType languageType) { this.Language = LanguageFactory.Create(languageType); }

        private void SetForeignKeyModel(EventsModel model)
        {
            if (model == null) return;
            model.Sponsor = new SponsorDAL(this.Language.LanguageType).GetSponsorModelByID(model.SponsorID);
            model.ArtForm = new ArtFormDAL(this.Language.LanguageType).GetArtFormModelByID(model.ArtFormID);
            model.CityModel = (model.City.HasValue) ? DictionaryUtil.GetCityByID(model.City.Value) : null;
            model.CommentsNum = new EventsCommentDAL(this.Language.LanguageType).GetTotalCountByEventsID(model.ID).ToString();
            model.PhotosNum = new EventsImageDAL(this.Language.LanguageType).GetTotalCountByEventsID(model.ID).ToString();
            model.VideosNum = new EventsVideoDAL(this.Language.LanguageType).GetTotalCountByEventsID(model.ID).ToString();
            model.FavoriteNum = new EventCalendarDAL().GetLikeCountByEventID(int.Parse(model.ID.ToString()));
            //model.PhoneImages = new EventsPhoneImageDAL(this.Language.LanguageType).getNameurl(model.ID);

            model.PhoneImagesList = new EventsPhoneImageDAL(this.Language.LanguageType).GetEventPhoneImages(model.ID);

            DataTable dt=getEventPraiseCount(null,model.ID,null,null).DataSet.Tables[0];
            model.PraiseNum = dt!=null?dt.Rows.Count:0;
            object obj = GetSEOInfo(model.ID.Value).ReturnObject;
            if (obj != null)
            {
                IStaticGeneratableModel igm = (IStaticGeneratableModel)obj;
                model.IsHtmlFile = igm.IsHtmlFile;
                model.HtmlFileName = igm.HtmlFileName;
                model.SEOMetaDescription = igm.SEOMetaDescription;
                model.SEOMetaKeywords = igm.SEOMetaKeywords;
                model.SEOTitle = igm.SEOTitle;
            }
        }

        #region IDALEvents Members
        /// <summary>
        /// 获取特色活动分页
        /// </summary>
        /// <param name="pagingParams">分页参数类</param>
        /// <returns>返回PagingList</returns>
        public PagingList GetFeaturedModel(PagingParameter pagingParams)
        {
            WhereClauseBuilder builder = WhereClauseBuilder.CreateInstance(SearchConditionJoinOperator.AND);
            builder.Conditions.Add(CommonSearchCondition.CreateInstance(COL_LANG_ID, (int)this.Language.LanguageType,SearchConditionOperator.Equals));
            builder.Conditions.Add(CommonSearchCondition.CreateInstance(COL_EVENTS_SERIES_ISSPECIAL, 1,SearchConditionOperator.Equals));

            string whereClause = builder.ToSqlCommand();
            whereClause += string.Format("and {0}=1", COL_EVENTS_COL_TOP);
            if (pagingParams == null)
                pagingParams = PagingParameterFactory.CreateNonPagingParameter(null);
            pagingParams.SortingParameter = SortingParameterFactory.CreateInstance(Order.PropertyName + "," + UpdatedTime.PropertyName, SortOrder.Desc);

            PagingList list = GetModels(whereClause, pagingParams);
            foreach (EventsModel model in list.List)
                SetForeignKeyModel(model);

            return list;
        }

        public EventsModel GetEventsModelByID(int? id)
        {
            EventsModel model = (EventsModel)GetModelByPrimaryKey(this.GetType(), new KeyValuePair<IField, object>[] { new KeyValuePair<IField, object>(LanguageID, (int)this.Language.LanguageType), new KeyValuePair<IField, object>(this.ID, id) });
            SetForeignKeyModel(model);
            return model;
        }

        #region 后台专用
        public IList<EventsModel> AfterGetAllEventsModels()
        {
            string whereClause = string.Format("where {0}={1}", COL_LANG_ID, (int)this.Language.LanguageType);
            IList<EventsModel> models = GetModels(whereClause, PagingParameterFactory.CreateNonPagingParameter(SortingParameterFactory.CreateInstance(Order.PropertyName + "," + ID.PropertyName, SortOrder.Desc))).List.Cast<EventsModel>().ToList();
            return models;
        }


        public IList<EventsModel> AfterGetAllEventsModels(int isSpecial)
        {
            string whereClause = string.Format("where {0}={1} and {2}={3} ", COL_LANG_ID, (int)this.Language.LanguageType, COL_EVENTS_SERIES_ISSPECIAL, isSpecial);

            IList<EventsModel> models = GetModels(whereClause, PagingParameterFactory.CreateNonPagingParameter(SortingParameterFactory.CreateInstance(Order.PropertyName + "," + ID.PropertyName, SortOrder.Desc))).List.Cast<EventsModel>().ToList();
            //foreach (EventsModel model in models)
            //    SetForeignKeyModel(model);

            return models;
        }

        public IList<EventsModel> AfterGetEventsModelByName(string ename, int? state, int? isspecial)
        {
            StringBuilder stb = new StringBuilder();
            stb.AppendFormat("where COL_LANG_ID={0} and COL_EVENTS_TITLE like '%{1}%' ", ((int)this.Language.LanguageType).ToString(), ename);
            if (state == 0)
            {
                stb.AppendFormat("and  COL_EVENTS_STATE={0}", state);
            }
            else
            {
                if (state == 1)
                {
                    stb.AppendFormat("and  COL_EVENTS_STATE={0}", state);
                }
                else
                { stb.Append("and  COL_EVENTS_STATE in (0,1)"); }

            }
            if (isspecial != null)
            {

                stb.AppendFormat("and  COL_EVENTS_SERIES_ISSPECIAL={0}", isspecial);
            }
            string whereClause = stb.ToString();
            IList<EventsModel> models = GetModels(whereClause, PagingParameterFactory.CreateNonPagingParameter(SortingParameterFactory.CreateInstance(ID.PropertyName, SortOrder.Asc))).List.Cast<EventsModel>().ToList();
            foreach (EventsModel model in models)
                SetForeignKeyModel(model);

            return models;
        }
        #endregion

        /// <summary>
        /// 去掉特色活动的限制 2012-6-18 李学明
        /// </summary>
        /// <returns></returns>
        public IList<EventsModel> GetAllEventsModels()
        {
            string whereClause = string.Format("where {0}={1} ", COL_LANG_ID, (int)this.Language.LanguageType);//and ISNULL(COL_EVENTS_SERIES_ISSPECIAL,0)!=1
            /*修改人：穆凤凤
        * 修改时间：2012-02-28
        * 修改内容：
        *      修改参数 活动列表 按id升序排列
        *      有mff注释的为本次修改的代码
        */
            //mff
            IList<EventsModel> models = GetModels(whereClause, PagingParameterFactory.CreateNonPagingParameter(SortingParameterFactory.CreateInstance(Order.PropertyName + "," + ID.PropertyName, SortOrder.Desc))).List.Cast<EventsModel>().ToList();
            //IList<EventsModel> models = GetModels(whereClause, PagingParameterFactory.CreateNonPagingParameter(SortingParameterFactory.CreateInstance(ID.PropertyName,null))).List.Cast<EventsModel>().ToList();
            foreach (EventsModel model in models)
                SetForeignKeyModel(model);

            return models;
        }
        //获得特色活动列表
        public IList<EventsModel> GetAllEventsModels(int isSpecial)
        {
            string whereClause = string.Format("where {0}={1} and {2}={3} ", COL_LANG_ID, (int)this.Language.LanguageType, COL_EVENTS_SERIES_ISSPECIAL, isSpecial);

            IList<EventsModel> models = GetModels(whereClause, PagingParameterFactory.CreateNonPagingParameter(SortingParameterFactory.CreateInstance(Order.PropertyName + "," + ID.PropertyName, SortOrder.Desc))).List.Cast<EventsModel>().ToList();
            foreach (EventsModel model in models)
                SetForeignKeyModel(model);

            return models;
        }
        //根据具体查找条件,获得活动的记录
        //mff
        public IList<EventsModel> GetEventsModelByName(string ename,int? state,int? isspecial)
        {
            StringBuilder stb = new StringBuilder();
            stb.AppendFormat("where COL_LANG_ID={0} and COL_EVENTS_TITLE like '%{1}%' ",((int)this.Language.LanguageType).ToString(), ename);
            if (state == 0)
            {
                stb.AppendFormat("and  COL_EVENTS_STATE={0}", state);
            }
            else
            {
                if (state == 1)
                {
                    stb.AppendFormat("and  COL_EVENTS_STATE={0}", state);
                }
                else
                { stb.Append("and  COL_EVENTS_STATE in (0,1)"); }

            }
            if (isspecial!=null)
            {

                stb.AppendFormat("and  COL_EVENTS_SERIES_ISSPECIAL={0}", isspecial);
            }
            string whereClause = stb.ToString();
            IList<EventsModel> models = GetModels(whereClause, PagingParameterFactory.CreateNonPagingParameter(SortingParameterFactory.CreateInstance(ID.PropertyName, SortOrder.Asc))).List.Cast<EventsModel>().ToList();
            foreach (EventsModel model in models)
                SetForeignKeyModel(model);

            return models;
        }
        //mff
        /*修改人：穆凤凤
        * 修改时间：2012-03-08
        * 修改内容：
        *      新增方法 获得按活动语言查询出的实体 GetEventModelsByName,用于图片和视频的同步
        *      有mff注释的为本次修改的代码
        */
        //mff
        public List<EventsModel> GetEventModelsByName(int langid)
        {
            string whereClause = "select distinct col_events_title from uknow_events where col_lang_id=" + langid + "order by col_events_title";

              DataSet ds=DBHelper.ExecuteDataSet(whereClause,null);
              DataTable dt=ds.Tables[0];
              List<EventsModel> iLevents = new List<EventsModel>();
              EventsModel em = null;
              if (dt.Rows.Count>0)
              {
                  foreach (DataRow dr in dt.Rows)
                  {
                      em = new EventsModel();
                      em.Title = dr["col_events_title"].ToString();
                      iLevents.Add(em);
                  }
              }
              return iLevents;
            
        }
        public IList<EventsModel> GetEveByNandL(int langid, string eventName)
        {
            string whereClause = string.Format("where {0}={1} and {2} like '%{3}%'", COL_LANG_ID, langid, COL_EVENTS_TITLE, eventName.Replace("'", "''"));
       
            IList<EventsModel> models = GetModels(whereClause, PagingParameterFactory.CreateNonPagingParameter(SortingParameterFactory.CreateInstance(ID.PropertyName, SortOrder.Asc))).List.Cast<EventsModel>().ToList();
            foreach (EventsModel model in models)
                SetForeignKeyModel(model);

            return models;
        }
        //mff
        public IList<EventsModel> GetPublishedEventsModels(int isSpecial)
        {
            WhereClauseBuilder builder = WhereClauseBuilder.CreateInstance(SearchConditionJoinOperator.AND);
            builder.Conditions.Add(CommonSearchCondition.CreateInstance(COL_EVENTS_STATE, 1, SearchConditionOperator.Equals));
            builder.Conditions.Add(CommonSearchCondition.CreateInstance(COL_LANG_ID, (int)this.Language.LanguageType, SearchConditionOperator.Equals));
            if (isSpecial==1)
                builder.Conditions.Add(CommonSearchCondition.CreateInstance(COL_EVENTS_SERIES_ISSPECIAL, 1, SearchConditionOperator.Equals));
            else
                builder.Conditions.Add(CommonSearchCondition.CreateInstance(string.Format("isnull({0}, 0)", COL_EVENTS_SERIES_ISSPECIAL), 0, SearchConditionOperator.Equals));

            string whereClause = builder.ToSqlCommand();
            IList<EventsModel> models = GetModels(whereClause, PagingParameterFactory.CreateNonPagingParameter(SortingParameterFactory.CreateInstance(UpdatedTime.PropertyName, SortOrder.Desc))).List.Cast<EventsModel>().ToList();
            foreach (EventsModel model in models)
                SetForeignKeyModel(model);

            return models;
        }

        public PagingList GetCalendarDefaultEvents(int? cityID, PagingParameter pagingParams)
        {
            IField[] fields = new IField[] { LanguageID, ID, State, Title, SponsorID, ArtFormID, City, Venue, StartTime, EndTime, ShortDescription, IsHot, IsRecommendation, Order, IsHomepageTop, IsColumnTop, ColumnID, LayerIDs, InsertedTime, UpdatedTime, ListImg, TopImg, TemplateID, TicketUrl, VenueImagePath };
            StringBuilder builder = new StringBuilder();

            if (cityID.HasValue)
            {
                string cmdSel1 = string.Format( "select {4} from {0} where {1}={2} and {3}>getdate() and {5}={6} order by {2} asc", COL_EVENTS_TIME_END, COL_EVENTS_TIME_START, "EventsEndTime", TBL_NAME, COL_EVENTS_ID, COL_EVENTS_CITY, cityID.Value);

            // 没有结束的活动按照开始时间正排序
            string cmdSel2 = string.Format("select {4}, isnull({0}, {1}) as {2} from {3} where isnull({0},{1})>=getdate() and {5}={6} order by {2} asc", COL_EVENTS_TIME_END, COL_EVENTS_TIME_START, "EventsEndTime", TBL_NAME, COL_EVENTS_ID, COL_EVENTS_CITY, cityID.Value);

            // 已经结束的活动按照结束时间倒排序
            string cmdSel3 = string.Format("select {4}, isnull({0},{1}) as {2} from {3} where isnull({0},{1})<getdate() and {5}={6} order by {2} asc", COL_EVENTS_TIME_END, COL_EVENTS_TIME_START, "EventsEndTime", TBL_NAME, COL_EVENTS_ID, COL_EVENTS_CITY, cityID.Value);
            builder.Append(cmdSel1).Append(cmdSel2).Append(cmdSel3);
            }

            string cmdSel4 = string.Format( "select {4} from {0} where {1}={2} and {3}>getdate() order by {2} asc", COL_EVENTS_TIME_END, COL_EVENTS_TIME_START, "EventsEndTime", TBL_NAME, COL_EVENTS_ID);

            // 没有结束的活动按照开始时间正排序
            string cmdSel5 = string.Format("select {4}, isnull({0}, {1}) as {2} from {3} where isnull({0},{1})>=getdate() order by {2} asc", COL_EVENTS_TIME_END, COL_EVENTS_TIME_START, "EventsEndTime", TBL_NAME, COL_EVENTS_ID);

            // 已经结束的活动按照结束时间倒排序
            string cmdSel6 = string.Format("select {4}, isnull({0},{1}) as {2} from {3} where isnull({0},{1})<getdate() order by {2} asc", COL_EVENTS_TIME_END, COL_EVENTS_TIME_START, "EventsEndTime", TBL_NAME, COL_EVENTS_ID);

            string whereClause = string.Format("where {0} in ({1}) and {2}={3}", COL_EVENTS_ID, builder.ToString(), COL_LANG_ID, (int)this.Language.LanguageType);
            PagingList models = GetModels(whereClause, pagingParams);
            return models;
        }

        /// <summary>
        ///     获取系列活动主活动数据
        /// </summary>
        /// <returns></returns>
        public IList<EventsModel> GetSeriesMainEvents()
        {
            string whereClause = string.Format("where {0}={1} and {2}={3}", COL_EVENTS_SERIES_MAIN, 1, COL_LANG_ID, (int)this.Language.LanguageType);
            return GetModels(whereClause, PagingParameterFactory.CreateNonPagingParameter(SortingParameterFactory.CreateInstance(UpdatedTime.PropertyName, SortOrder.Desc))).List.Cast<EventsModel>().ToList();
        }

        /// <summary>
        ///     获取活动ID为eventsID的活动的相关活动数据 2012-5-8 李学明 处理排序的问题
        /// </summary>
        /// <param name="eventsID"></param>
        /// <returns></returns>
        public IList<EventsModel> GetEventsModelsByEventsIDs(IList<int?> ids)
        {
            WhereClauseBuilder builder = WhereClauseBuilder.CreateInstance(SearchConditionJoinOperator.AND);
            builder.Conditions.Add(CommonSearchCondition.CreateInstance(COL_LANG_ID, (int)this.Language.LanguageType, SearchConditionOperator.Equals));
            string whereClause = builder.ToSqlCommand();
            var orderstr = "";
            if (ids.Count > 0)
            {
                whereClause += " and " + COL_EVENTS_ID + " in (";
                int i = 0;
                foreach (int? id in ids)
                {
                    if (i > 0)
                    {
                        orderstr += ",";
                        whereClause += ",";
                    }
                    orderstr += id;
                    whereClause += id;
                    i++;
                }
                whereClause += ")   order by charindex(cast(COL_EVENTS_ID as varchar(20)),N','+'"+orderstr+"'+N',')";
            }

            IList<EventsModel> models = GetModels(whereClause, PagingParameterFactory.CreateNonPagingParameter(SortingParameterFactory.CreateNonSortingParameter())).List.Cast<EventsModel>().ToList();
            foreach (EventsModel model in models)
                SetForeignKeyModel(model);

            return models;
        }

        /// <summary>
        ///     获取活动ID为eventsID的活动的系列活动数据
        /// </summary>
        /// <param name="eventsID"></param>
        /// <returns></returns>
        public IList<EventsModel> GetSeriesEvents(int? eventsID, bool includeSelf)
        {
            IList<EventsModel> events = new List<EventsModel>();
            EventsModel model = GetEventsModelByID(eventsID);
            if (model == null) return events;
            if (!model.SeriesMainEventsID.HasValue) return events;

            WhereClauseBuilder builder = WhereClauseBuilder.CreateInstance(SearchConditionJoinOperator.AND);
            builder.Conditions.Add(CommonSearchCondition.CreateInstance(COL_LANG_ID, (int)this.Language.LanguageType, SearchConditionOperator.Equals));
            builder.Conditions.Add(CommonSearchCondition.CreateInstance(COL_EVENTS_SERIES_ID, model.SeriesMainEventsID, SearchConditionOperator.Equals));
            builder.Conditions.Add(CommonSearchCondition.CreateInstance(COL_EVENTS_ID, eventsID, SearchConditionOperator.NotEquals));
            builder.Conditions.Add(CommonSearchCondition.CreateInstance(COL_EVENTS_STATE, 1, SearchConditionOperator.Equals));

            string whereClause = builder.ToSqlCommand();
            events = GetModels(whereClause, null).List.Cast<EventsModel>().ToList();
            foreach (EventsModel evt in events)
                SetForeignKeyModel(evt);
            return events;
        }

        /// <summary>
        ///     
        /// </summary>
        /// <param name="yearMonth">形如：2012-02的年月格式</param>
        /// <returns></returns>
        public IList<DictionaryManageModel> GetEventsCitiesByYearMonth(string yearMonth)
        {
            string sql = "select distinct {0}, count({1}) as eventscount from {2} where " + COL_EVENTS_STATE + "=1 and " + COL_LANG_ID + "=" + (int)this.Language.LanguageType;
            string cmd = null;
            if (!string.IsNullOrEmpty(yearMonth) && yearMonth != "null" && yearMonth != "0" && yearMonth.Length == 7)
            {
                // 获取活动举办时间在startDate和endDate间活动的方法
                // 活动开始时间<startDate and 活动结束时间>startDate
                // 活动开始时间>startDate and 活动开始时间<endDate
                string startDate = yearMonth + "-01";
                string endDate = DateTimeUtility.GetEndDateOfMonth(yearMonth);
                sql += " and ((DATEDIFF(dd, {3}, '{4}') >=0  and DATEDIFF(dd, {5}, '{4}') <=0) or (DATEDIFF(dd, {3}, '{4}') <=0  and DATEDIFF(dd, {3}, '{6}') >=0)) " +
                    "group by {0} order by eventscount desc";
                cmd = string.Format(sql, COL_EVENTS_CITY, COL_EVENTS_ID, TBL_NAME, COL_EVENTS_TIME_START, startDate, COL_EVENTS_TIME_END, endDate);
            }
            else
            {
                sql += " group by {0} order by eventscount desc";
                cmd = string.Format(sql, COL_EVENTS_CITY, COL_EVENTS_ID, TBL_NAME);
            }

            List<int> cityIDs = new List<int>();
            using (IDataReader reader = DBHelper.ExecuteReader(cmd))
            {
                while (reader.Read())
                {
                    if (reader[COL_EVENTS_CITY] != DBNull.Value)
                        cityIDs.Add((int)reader[COL_EVENTS_CITY]);
                }
            }
            
            StringBuilder builder = new StringBuilder(" cid in (");
            int i = 0;
            foreach (int cityID in cityIDs)
            {
                if (i > 0)
                    builder.Append(",");
                builder.Append(cityID);
                i++;
            }
            if (i == 0)
                builder.Append("''");
            builder.Append(")");

            DictionaryManage dm = new DictionaryManage();
            DataSet dataSet = dm.GetDictionary(builder.ToString());
            if (dataSet.Tables.Count == 0 || dataSet.Tables[0].Rows.Count == 0) return null;

            IList<DictionaryManageModel> list = new List<DictionaryManageModel>();
            foreach (DataRow dr in dataSet.Tables[0].Rows)
            {
                DictionaryManageModel model = new DictionaryManageModel();
                model.Cid = (int)dr["cid"];
                model.Cnname = (string)dr["cnname"];
                model.Enname = (string)dr["enname"];
                list.Add(model);
            }

            return list;
        }

        public IList<int> GetEventsDayInPeriod(string startDate, string endDate)
        {
            // 两种情况满足条件：
            // 1. col_events_time_start <= startDate and col_events_time_end >= startDate
            // 2. col_events_time_start >= startDate and col_events_time_start <= endDate
            string cmd = string.Format("select {0}, {3}, datediff(dd, {0}, {3}) as d_diff from {1} where ((datediff(dd, {0}, '{2}')>=0 and datediff(dd, {3}, '{2}')<=0) or (datediff(dd, {0}, '{2}')<=0 and datediff(dd, {0}, '{4}')>=0)) and {5}={6} and {7}=1 and {0} is not null",
                COL_EVENTS_TIME_START, TBL_NAME, startDate, COL_EVENTS_TIME_END, endDate, COL_LANG_ID, (int)this.Language.LanguageType, COL_EVENTS_STATE);

            IList<int> list = new List<int>();
            using (IDataReader reader = DBHelper.ExecuteReader(cmd))
            {
                DateTime? startTime, endTime;
                int dateDiff = 0;
                while (reader.Read())
                {
                    startTime = (DateTime?)reader[COL_EVENTS_TIME_START];
                    endTime = (reader[COL_EVENTS_TIME_END] == DBNull.Value) ? null : (DateTime?)reader[COL_EVENTS_TIME_END];
                    if (endTime.HasValue) dateDiff = (int)reader["d_diff"];

                    if (!list.Contains(startTime.Value.Day)) list.Add(startTime.Value.Day);

                    while (dateDiff > 0)
                    {
                        if (startTime.Value.AddDays(dateDiff) <= DateTime.Parse(endDate) && startTime.Value.AddDays(dateDiff) >= DateTime.Parse(startDate))
                            if (!list.Contains(startTime.Value.AddDays(dateDiff).Day)) list.Add(startTime.Value.AddDays(dateDiff).Day);
                        dateDiff--;
                    }
                }
            }

            return list;
        }

        /// <summary>
        /// 处理日期显示过多的问题
        /// </summary>
        /// <param name="cityID"></param>
        /// <param name="artFormID"></param>
        /// <returns></returns>
        public IList<string> GetAllPublishedEventsYearMonth(int cityID, int artFormID)
        {
            EventsDateDAL dal = new EventsDateDAL(this.Language.LanguageType);

            string filterCityClause = cityID == 0 ? "1=1" : COL_EVENTS_CITY + "=" + cityID;
            string filterArtFormClause = artFormID == 0 ? "1=1" : COL_EVENTS_FORM + "=" + artFormID;

            string cmd = string.Format("select distinct SUBSTRING({0}, 1, 7) as yearmonth from {1} where {2} in (select {2} from {3} where {4}=1 and {5} and {6} and {7}={8}) and {7}={8} order by yearmonth",
                dal.Time.Column.Name, dal.TableName, COL_EVENTS_ID, TableName, COL_EVENTS_STATE, filterCityClause, filterArtFormClause, COL_LANG_ID, (int)this.Language.LanguageType);

            IList<string> list = new List<string>();
            using (IDataReader reader = DBHelper.ExecuteReader(cmd))
            {
                while (reader.Read())
                {
                    object obj = reader["yearmonth"];
                    if (obj == null || obj == DBNull.Value) continue;
                    list.Add((string)obj);
                }
            }
            return list;
        }

        /// <summary>
        /// 2012-5-10 处理了月份对应的有活动的日期过多的问题 李学明
        /// </summary>
        /// <param name="cityID"></param>
        /// <param name="artFormID"></param>
        /// <param name="yearMonth"></param>
        /// <returns></returns>
        public IList<int> GetAllPublishedEventsDate(int cityID, int artFormID, string yearMonth)
        {
            EventsDateDAL dal = new EventsDateDAL(this.Language.LanguageType);

            string filterCityClause = cityID == 0 ? "1=1" : COL_EVENTS_CITY + "=" + cityID;
            string filterArtFormClause = artFormID == 0 ? "1=1" : COL_EVENTS_FORM + "=" + artFormID;
            string filterYearMonthClause = yearMonth == "0" ? "1=1" : string.Format("substring({0},1,7)='{1}'", dal.Time.Column.Name, yearMonth);

            string cmd = string.Format("select distinct {0} from {1} where {2} and {3} in (select {3} from {4} where {5}={6} and {7}={8} and {9} and {10})  and {7}={8} order by {0}",
                dal.Time.Column.Name, dal.TableName, filterYearMonthClause, COL_EVENTS_ID, TableName, COL_EVENTS_STATE, 1
                , COL_LANG_ID, (int)this.Language.LanguageType, filterCityClause, filterArtFormClause);

            IList<int> list = new List<int>();
            using (IDataReader reader = DBHelper.ExecuteReader(cmd))
            {
                while (reader.Read())
                {
                    object obj = reader[dal.Time.Column.Name];
                    if (obj == null || obj == DBNull.Value) continue;
                    string s = (string)obj;
                    if (string.IsNullOrEmpty(s)) continue;
                    if (s.Length != 10) continue;
                    s = s.Substring(8);
                    int date = s.StartsWith("0") ? int.Parse(s.Substring(1)) : int.Parse(s);
                    list.Add(date);
                }
            }
            return list;
        }

        public IList<ArtFormModel> GetAllPublishedEventsArtForms(int cityID)
        {
            string cmd = string.Format("select distinct {0} from {1} where {2}={3} and {4}=1 ", COL_EVENTS_FORM, TableName, COL_LANG_ID, (int)this.Language.LanguageType, COL_EVENTS_STATE);
            if (cityID > 0)
                cmd += string.Format(" and {0}={1} ", COL_EVENTS_CITY, cityID);

            IList<ArtFormModel> list = new List<ArtFormModel>();
            ArtFormDAL dal = new ArtFormDAL(this.Language.LanguageType);
            using (IDataReader reader = DBHelper.ExecuteReader(cmd))
            {
                while (reader.Read())
                {
                    object obj = reader[COL_EVENTS_FORM];
                    if (obj == null || obj == DBNull.Value) continue;
                    list.Add(dal.GetArtFormModelByID((int)obj));
                }
            }
            return list;
        }

        /*修改人：穆凤凤
    * 修改时间：2012-04-17
    * 修改内容：
    *      新增方法 获得"赞"活动的有关信息,多表查询得到
    *      有mff注释的为本次修改的代码
    */
        //mff
        /// <summary>
        ///     获得"赞"活动的有关信息的所有记录,若传入的参数不为空,则根据参数获得符合条件的记录
        /// </summary>
        /// <returns></returns>
        public PagingDataSet getEventPraiseCount(int? langID, int? memberID, int? CurrentPageIndex, int? PageSize)
        {
            string sqlcmd = @"select a.MEMBER_ID as uid,b.USERNAME as username,b.MEMBER_HERD_IMG as avatar ,c.COL_EVENTS_ID as eventid,c.COL_EVENTS_TITLE as  title,c.COL_EVENTS_SPONSOR as sponsor,c.COL_EVENTS_FORM as artname,
                              c.COL_EVENTS_DETAIL as text,c.COL_EVENTS_LB_IMG as listimg,c.COL_EVENTS_TOP_IMG as topimg,c.COL_EVENTS_TICKET_URL as ticketurl,c.COL_EVENTS_INC_HTML as inctext,c.COL_EVENTS_TIME_START as startdate ,c.COL_EVENTS_TIME_END as enddate
                              from UKNOW_MEMBER_PraiseEVENTS as a inner join UKNOW_MEMBER_INFO as b 
                              on a.MEMBER_ID=b.MEMBER_ID inner join UKNOW_EVENTS as c on c.COL_EVENTS_ID=a.COL_EVENTS_ID ";
            if (langID.HasValue)
            {
                sqlcmd += "where a.COL_LANG_ID=" + langID;
                if (memberID.HasValue)
                { sqlcmd += "and a.MEMBER_ID=" + memberID; }
            }
            else
            {
                if (memberID.HasValue)
                { sqlcmd += "where a.MEMBER_ID=" + memberID; }
            }

            DataSet ds = null;
            
            int totalRecordCount;
            if (PageSize.HasValue && CurrentPageIndex.HasValue)
            {
                ds = DBHelper.ExecuteDataSet(sqlcmd, int.Parse(CurrentPageIndex.ToString()), int.Parse(PageSize.ToString()), out totalRecordCount);
            }
            else
            {
                ds = DBHelper.ExecuteDataSet(sqlcmd);
                totalRecordCount = ds.Tables[0].Rows.Count;
            }

            return new PagingDataSet(ds, totalRecordCount);
        }

        public PagingDataSet getUpEventByUserID(int? langID, int? userID, int? currentPageIndex, int? pageSize)
        {
            string sqlcmd = @"select distinct col_lang_id, col_events_id from UKNOW_MEMBER_PraiseEVENTS ";
            if (langID.HasValue)
            {
                sqlcmd += "where COL_LANG_ID=" + langID;
                if (userID.HasValue)
                { sqlcmd += "and MEMBER_ID=" + userID; }
            }
            else
            {
                if (userID.HasValue)
                { sqlcmd += "where MEMBER_ID=" + userID; }
            }

            DataSet ds = null;

            int totalRecordCount;
            if (pageSize.HasValue && currentPageIndex.HasValue)
            {
                ds = DBHelper.ExecuteDataSet(sqlcmd, int.Parse(currentPageIndex.ToString()), int.Parse(pageSize.ToString()), out totalRecordCount);
            }
            else
            {
                ds = DBHelper.ExecuteDataSet(sqlcmd);
                totalRecordCount = ds.Tables[0].Rows.Count;
            }

            return new PagingDataSet(ds, totalRecordCount);
        }

        /// <summary>
        /// 修改时间 2012-5-10 李学明 处理相关的活显示活动已过期的问题
        /// </summary>
        /// <param name="eventsID"></param>
        /// <param name="pagingParams"></param>
        /// <returns></returns>
        public IList<EventsModel> GetRelatedEvents(int? eventsID, PagingParameter pagingParams)
        {
            IList<EventsModel> models = new List<EventsModel>();
            EventsModel currEventInfo = GetEventsModelByID(eventsID);
            if (currEventInfo == null) return models;

            if (pagingParams == null)
                pagingParams = PagingParameterFactory.CreateInstance(true, 0, 3, SortingParameterFactory.CreateInstance(StartTime.PropertyName, SortOrder.Asc));

            List<int> ids = new List<int>();
            string timeWhereClause = " 1=1 ";
            //if (currEventInfo.StartTime != null && currEventInfo.EndTime != null)
            //{
            //    timeWhereClause = string.Format(" ((DATEDIFF(dd, {0}, '{1}') >=0  and DATEDIFF(dd, {2}, '{1}') <=0) or (DATEDIFF(dd, {0}, '{1}') <=0  and DATEDIFF(dd, {0}, '{3}') >=0)) ",
            //        COL_EVENTS_TIME_START, currEventInfo.StartTime.Value.ToString("yyyy-MM-dd"), COL_EVENTS_TIME_END, currEventInfo.EndTime.Value.ToString("yyyy-MM-dd"));
            //}
            //else if (currEventInfo.StartTime != null)
            //{
            //    timeWhereClause = string.Format(" ((col_events_time_end is null and datediff(dd, col_events_time_start, '{0}')=0) " +
            //                        "or (" +
            //                            "col_events_time_end is not null " +
            //                            "and datediff(dd, col_events_time_end, '{0}')<=0 " +
            //                            "and datediff(dd, col_events_time_start, '{0}')>=0 " +
            //                        "))", currEventInfo.StartTime.Value.ToString("yyyy-MM-dd"));
            //}

            // 首先找出具有相关性的活动
            string whereClause = string.Format(" where {0}={1} and {2}={3} and {4}<>{5} and col_events_time_start is not null and {6}='{7}' and {8}='{9}' and {10} and {4} in {11} ",
                COL_LANG_ID, (int)this.Language.LanguageType,
                COL_EVENTS_STATE, 1,
                COL_EVENTS_ID, eventsID,
                COL_EVENTS_FORM, (currEventInfo.ArtFormID.HasValue) ? currEventInfo.ArtFormID.Value : -1,
                COL_EVENTS_CITY, (currEventInfo.City.HasValue) ? currEventInfo.City.Value : -1, timeWhereClause,
                " (select  COL_EVENTS_ID from   dbo.UKNOW_EVENTS_DATE where  COL_EVENTS_DATE>='" + DateTime.Now.ToString("yyyy-MM-dd") + "' and COL_LANG_ID="+(int)this.Language.LanguageType+"  group by COL_EVENTS_ID)"
                );

            ReturnEventsModelsByWhereClause(whereClause, pagingParams, models, ids);

            if (models.Count < pagingParams.PageSize)
            {
                whereClause = string.Format(" where {0}={1} and {2}={3} and {4}<>{5} and col_events_time_start is not null and {6}='{7}' and {8} and {4} in {9}  ",
                    COL_LANG_ID, (int)this.Language.LanguageType,
                    COL_EVENTS_STATE, 1,
                    COL_EVENTS_ID, eventsID,
                    COL_EVENTS_CITY, (currEventInfo.City.HasValue) ? currEventInfo.City.Value : -1, timeWhereClause,
                   "(select  COL_EVENTS_ID from   dbo.UKNOW_EVENTS_DATE where  COL_EVENTS_DATE>='" + DateTime.Now.ToString("yyyy-MM-dd") + "' and COL_LANG_ID=" + (int)this.Language.LanguageType + "  group by COL_EVENTS_ID)");

                ReturnEventsModelsByWhereClause(whereClause, pagingParams, models, ids);
            }

            if (models.Count < pagingParams.PageSize)
            {
                whereClause = string.Format(" where {0}={1} and {2}={3} and {4}<>{5} and col_events_time_start is not null and {6}='{7}' and {8} and {4} in {9} ",
                    COL_EVENTS_STATE, 1,
                    COL_LANG_ID, (int)this.Language.LanguageType,
                    COL_EVENTS_ID, eventsID,
                    COL_EVENTS_FORM, (currEventInfo.ArtFormID.HasValue) ? currEventInfo.ArtFormID.Value : -1, timeWhereClause,
                    "(select  COL_EVENTS_ID from   dbo.UKNOW_EVENTS_DATE where  COL_EVENTS_DATE>='" + DateTime.Now.ToString("yyyy-MM-dd") + "' and COL_LANG_ID=" + (int)this.Language.LanguageType + "  group by COL_EVENTS_ID)");

                ReturnEventsModelsByWhereClause(whereClause, pagingParams, models, ids);
            }

            if (models.Count < pagingParams.PageSize)
            {
                whereClause = string.Format(" where {0}={1} and {2}={3} and {4}<>{5} and col_events_time_start is not null and {6} and {4} in {7} ",
                    COL_LANG_ID, (int)this.Language.LanguageType,
                    COL_EVENTS_STATE, 1,
                    COL_EVENTS_ID, eventsID,
                    timeWhereClause,
                     "(select  COL_EVENTS_ID from   dbo.UKNOW_EVENTS_DATE where  COL_EVENTS_DATE>='" + DateTime.Now.ToString("yyyy-MM-dd") + "' and COL_LANG_ID=" + (int)this.Language.LanguageType + "  group by COL_EVENTS_ID)");

                ReturnEventsModelsByWhereClause(whereClause, pagingParams, models, ids);
            }

            /*
             * 代码添加
             * 添加人员：刘扬
             * 添加日期：2012-03-13
             * 添加原因：如果增加了时间范围的筛选后，取不出3条相关热门活动，则去掉时间范围筛选条件
             * */
            if (models.Count < pagingParams.PageSize)
            {
                whereClause = string.Format(" where {0}={1} and {2}={3} and {4}<>{5} and {6}='{7}' and {8}='{9}' and {4} in {10} ",
                    COL_LANG_ID, (int)this.Language.LanguageType,
                    COL_EVENTS_STATE, 1,
                    COL_EVENTS_ID, eventsID,
                    COL_EVENTS_FORM, (currEventInfo.ArtFormID.HasValue) ? currEventInfo.ArtFormID.Value : -1,
                    COL_EVENTS_CITY, (currEventInfo.City.HasValue) ? currEventInfo.City.Value : -1,
                     "(select  COL_EVENTS_ID from   dbo.UKNOW_EVENTS_DATE where  COL_EVENTS_DATE>='" + DateTime.Now.ToString("yyyy-MM-dd") + "' and COL_LANG_ID=" + (int)this.Language.LanguageType + "  group by COL_EVENTS_ID)");

                ReturnEventsModelsByWhereClause(whereClause, pagingParams, models, ids);
            }

            if (models.Count < pagingParams.PageSize)
            {
                whereClause = string.Format(" where {0}={1} and {2}={3} and {4}<>{5} and {6}='{7}' and {4} in {8}  ",
                    COL_LANG_ID, (int)this.Language.LanguageType,
                    COL_EVENTS_STATE, 1,
                    COL_EVENTS_ID, eventsID,
                    COL_EVENTS_FORM, (currEventInfo.ArtFormID.HasValue) ? currEventInfo.ArtFormID.Value : -1,
                      "(select  COL_EVENTS_ID from   dbo.UKNOW_EVENTS_DATE where  COL_EVENTS_DATE>='" + DateTime.Now.ToString("yyyy-MM-dd") + "' and COL_LANG_ID=" + (int)this.Language.LanguageType + "  group by COL_EVENTS_ID)");
                ReturnEventsModelsByWhereClause(whereClause, pagingParams, models, ids);
            }

            if (models.Count < pagingParams.PageSize)
            {
                whereClause = string.Format(" where {0}={1} and {2}={3} and {4}<>{5} and {6}='{7}' and {4} in {8} ",
                    COL_LANG_ID, (int)this.Language.LanguageType,
                    COL_EVENTS_STATE, 1,
                    COL_EVENTS_ID, eventsID,
                    COL_EVENTS_CITY, (currEventInfo.City.HasValue) ? currEventInfo.City.Value : -1,
                      "(select  COL_EVENTS_ID from   dbo.UKNOW_EVENTS_DATE where  COL_EVENTS_DATE>='" + DateTime.Now.ToString("yyyy-MM-dd") + "' and COL_LANG_ID=" + (int)this.Language.LanguageType + "  group by COL_EVENTS_ID)");

                ReturnEventsModelsByWhereClause(whereClause, pagingParams, models, ids);
            }

            foreach (EventsModel model in models)
                SetForeignKeyModel(model);

            return models;
        }

        private void ReturnEventsModelsByWhereClause(string whereClause, PagingParameter pagingParams, IList<EventsModel> models, List<int> ids)
        {
            PagingList list = GetModels(whereClause, pagingParams);
            int count = models.Count;
            for (int i = 0; i < list.List.Count && i < pagingParams.PageSize - count; i++)
            {
                if (!ids.Contains(((EventsModel)list.List[i]).ID.Value))
                {
                    models.Add((EventsModel)list.List[i]);
                    ids.Add(((EventsModel)list.List[i]).ID.Value);
                }
            }
        }

        private string FormatYearMonth(DateTime? dateTime)
        {
            if (!dateTime.HasValue) return null;
            string startYM = dateTime.Value.Year + "-";
            if (dateTime.Value.Month < 10) startYM += "0";
            startYM += dateTime.Value.Month;
            return startYM;
        }

        public ResultInfo Save(EventsModel modelInfo)
        {
            if (modelInfo == null) throw new ArgumentNullException("modelInfo");
            ResultInfo result = new ResultInfo(false);

            try
            {
                int rowsAffected = 0;
                if (modelInfo.ID == null)   // insert
                {
                    modelInfo.ID = SqlUtil.GetMaxRecordID(TableName, COL_EVENTS_ID, COL_LANG_ID, (int)this.Language.LanguageType);
                    //modelInfo.State = 0;    // 强制将活动状态置为未发布
                    modelInfo.InsertedTime = DateTime.Now;
                    modelInfo.UpdatedTime = DateTime.Now;
                    if (modelInfo.IsSeriesMain)
                        modelInfo.SeriesMainEventsID = modelInfo.ID;
                    rowsAffected = Insert(modelInfo, null);
                }
                else // update
                {
                    modelInfo.UpdatedTime = DateTime.Now;
                    if (modelInfo.IsSeriesMain)
                        modelInfo.SeriesMainEventsID = modelInfo.ID;
                    rowsAffected = UpdateByPrimaryKey(modelInfo, null);
                }

                if (rowsAffected > 0)
                {
                    result.Message = "活动数据保存成功！";
                    result.Result = true;
                    result.ReturnObject = modelInfo;
                }
            }
            catch (Exception ex)
            {
                result.Exception = ex;
                result.Message = string.Format("保存活动数据时发生异常。异常信息：{0}", ex.Message);
            }

            return result;
        }

        public ResultInfo Delete(IList<int?> idList)
        {
            ResultInfo result = new ResultInfo(false);
            using (IDbConnection conn = DBHelper.GetDbConnection())
            {
                conn.Open();
                IDbTransaction trans = conn.BeginTransaction();
                try
                {
                    EventsCommentDAL ecd = new EventsCommentDAL(this.Language.LanguageType);
                    EventsImageDAL eid = new EventsImageDAL(this.Language.LanguageType);
                    EventsVideoDAL evd = new EventsVideoDAL(this.Language.LanguageType);
                    EventsLogDAL eld = new EventsLogDAL(this.Language.LanguageType);
                    foreach (int? id in idList)
                    {
                        /*
                         * 代码修改
                         * 修改人员：刘扬
                         * 修改日期：2012-02-14
                         * 修改描述：MD，情人节凌晨0:18分还在这里写程序，真是禽兽。
                         * */
                        ecd.DeleteByEventsID(trans, id);        // 删除活动评论
                        eid.DeleteByEventsID(trans, id);        // 删除活动图片
                        evd.DeleteByEventsID(trans, id);        // 删除活动视频
                        eld.DeleteByEventsID(trans, id);        // 删除活动浏览日志

                        // 删除活动
                        DeleteModelByPrimaryKey(trans, this.GetType(), new KeyValuePair<IField, object>[] { new KeyValuePair<IField, object>(LanguageID, (int)this.Language.LanguageType), new KeyValuePair<IField, object>(this.ID, id) });
                    }

                    trans.Commit();
                    result.Result = true;
                    result.Message = "活动数据删除成功！";
                }
                catch (Exception ex)
                {
                    trans.Rollback();
                    result.Message = string.Format("删除活动数据时发生异常。异常信息：{0}", ex.Message);
                }
                finally
                {
                    trans.Dispose();
                }
            }

            return result;
        }

        public ResultInfo Publish(IList<int?> idList)
        {
            ResultInfo result = new ResultInfo(false);

            IField[] updatedFields = new IField[] { this.State };
            IDataParameter[] parms = DBHelper.GetDataParameters(updatedFields);
            parms[0].Value = 1;

            string ids = "";
            foreach (int? id in idList)
                ids += (ids == string.Empty) ? id.ToString() : "," + id.ToString();

            WhereClauseBuilder builder = WhereClauseBuilder.CreateInstance(SearchConditionJoinOperator.AND);
            builder.Conditions.Add(InSearchCondition.CreateInstance(COL_EVENTS_ID, DataType.Int, ids));
            builder.Conditions.Add(CommonSearchCondition.CreateInstance(COL_LANG_ID, (int)this.Language.LanguageType, SearchConditionOperator.Equals));

            string whereClause = builder.ToSqlCommand();
            string cmdText = DBHelper.GetUpdateCommandText(this.TableName, updatedFields, whereClause);

            try
            {
                DBHelper.ExecuteNonQuery(cmdText, parms);
                result.Result = true;
                result.Message = "活动发布成功！";
            }
            catch (Exception ex)
            {
                result.Message = string.Format("发布活动时发生异常。异常信息：{0}", ex.Message);
            }

            return result;
        }

        public ResultInfo UnPublish(IList<int?> idList)
        {
            ResultInfo result = new ResultInfo(false);

            IField[] updatedFields = new IField[] { this.State };
            IDataParameter[] parms = DBHelper.GetDataParameters(updatedFields);
            parms[0].Value = 0;

            string ids = "";
            foreach (int? id in idList)
                ids += (ids == string.Empty) ? id.ToString() : "," + id.ToString();

            WhereClauseBuilder builder = WhereClauseBuilder.CreateInstance(SearchConditionJoinOperator.AND);
            builder.Conditions.Add(InSearchCondition.CreateInstance(COL_EVENTS_ID, DataType.Int, ids));
            builder.Conditions.Add(CommonSearchCondition.CreateInstance(COL_LANG_ID, (int)this.Language.LanguageType, SearchConditionOperator.Equals));

            string whereClause = builder.ToSqlCommand();
            string cmdText = DBHelper.GetUpdateCommandText(this.TableName, updatedFields, whereClause);

            try
            {
                DBHelper.ExecuteNonQuery(cmdText, parms);
                result.Result = true;
                result.Message = "活动取消发布成功！";
            }
            catch (Exception ex)
            {
                result.Message = string.Format("取消发布活动时发生异常。异常信息：{0}", ex.Message);
            }

            return result;
        }
        /// <summary>
        /// 根据城市ID获取活动
        /// </summary>
        /// <param name="CityID">城市ID</param>
        /// <returns></returns>
        public PagingList GetEventsByCityID(int CityID,PagingParameter pagingParams)
        {

           return   GetFilteredEventsModels(CityID, -1, "0", -1, pagingParams);
        }
        /// <summary>
        /// 根据城市ID和艺术形式ID获取活动
        /// </summary>
        /// <param name="CityID">城市ID</param>
        /// <param name="artID">艺术形式ID</param>
        /// <returns></returns>
        public PagingList GetEventsByCityIDAndArtID(int CityID, int artID, PagingParameter pagingParams)
        {
            return GetFilteredEventsModels(CityID, artID, "0", -1, pagingParams);
        }
        /// <summary>
        /// 根据城市ID和日期获取活动
        /// </summary>
        /// <param name="CityID"></param>
        /// <param name="date"></param>
        /// <returns></returns>
        public PagingList GetEventsByCityAndDate(int CityID, string yymm, PagingParameter pagingParams)
        {
            if (yymm.Length == 10)
            {
                int date = Convert.ToDateTime(yymm).Date.Day;
                yymm = yymm.Substring(0,7);
                return GetFilteredEventsModels(CityID, -1, yymm,date, pagingParams);
            }
            else
            {
                return GetFilteredEventsModels(CityID, -1, yymm, -1, pagingParams);
            }
        }



        public ResultInfo SetIsSpecial(IList<int?> idList)
        {
            ResultInfo result = new ResultInfo(false);

            IField[] updatedFields = new IField[] { this.IsSpecial };
            IDataParameter[] parms = DBHelper.GetDataParameters(updatedFields);
            parms[0].Value = 1;

            string ids = "";
            foreach (int? id in idList)
                ids += (ids == string.Empty) ? id.ToString() : "," + id.ToString();

            WhereClauseBuilder builder = WhereClauseBuilder.CreateInstance(SearchConditionJoinOperator.AND);
            builder.Conditions.Add(InSearchCondition.CreateInstance(COL_EVENTS_ID, DataType.Int, ids));
            builder.Conditions.Add(CommonSearchCondition.CreateInstance(COL_LANG_ID, (int)this.Language.LanguageType, SearchConditionOperator.Equals));

            string whereClause = builder.ToSqlCommand();
            string cmdText = DBHelper.GetUpdateCommandText(this.TableName, updatedFields, whereClause);

            try
            {
                DBHelper.ExecuteNonQuery(cmdText, parms);
                result.Result = true;
                result.Message = "特色活动修改成功！";
            }
            catch (Exception ex)
            {
                result.Message = string.Format("修改特色活动时发生异常。异常信息：{0}", ex.Message);
            }

            return result;
        }

        public ResultInfo SetNotSpecial(IList<int?> idList)
        {
            ResultInfo result = new ResultInfo(false);

            IField[] updatedFields = new IField[] { this.IsSpecial };
            IDataParameter[] parms = DBHelper.GetDataParameters(updatedFields);
            parms[0].Value = 0;

            string ids = "";
            foreach (int? id in idList)
                ids += (ids == string.Empty) ? id.ToString() : "," + id.ToString();

            WhereClauseBuilder builder = WhereClauseBuilder.CreateInstance(SearchConditionJoinOperator.AND);
            builder.Conditions.Add(InSearchCondition.CreateInstance(COL_EVENTS_ID, DataType.Int, ids));
            builder.Conditions.Add(CommonSearchCondition.CreateInstance(COL_LANG_ID, (int)this.Language.LanguageType, SearchConditionOperator.Equals));

            string whereClause = builder.ToSqlCommand();
            string cmdText = DBHelper.GetUpdateCommandText(this.TableName, updatedFields, whereClause);

            try
            {
                DBHelper.ExecuteNonQuery(cmdText, parms);
                result.Result = true;
                result.Message = "非特色活动修改成功！";
            }
            catch (Exception ex)
            {
                result.Message = string.Format("非特色活动修改时生异常。异常信息：{0}", ex.Message);
            }

            return result;
        }


        /// <summary>
        /// 根据城市ID，艺术形式，年月日获取活动 2012-5-10 处理活动的显示顺序，将要发生的活活显示在前面 李学明
        /// </summary>
        /// <param name="cityID"></param>
        /// <param name="artFormID"></param>
        /// <param name="yearMonth"></param>
        /// <param name="date"></param>
        /// <param name="pagingParams"></param>
        /// <returns></returns>
        public PagingList GetFilteredEventsModels(int cityID, int artFormID, string yearMonth, int date, PagingParameter pagingParams)
        {
            WhereClauseBuilder builder = WhereClauseBuilder.CreateInstance(SearchConditionJoinOperator.AND);
            builder.Conditions.Add(CommonSearchCondition.CreateInstance("a."+COL_LANG_ID, (int)this.Language.LanguageType, SearchConditionOperator.Equals));
            builder.Conditions.Add(CommonSearchCondition.CreateInstance("a."+COL_EVENTS_STATE, 1, SearchConditionOperator.Equals));

            if (cityID > 0)
                builder.Conditions.Add(CommonSearchCondition.CreateInstance("a."+COL_EVENTS_CITY, cityID, SearchConditionOperator.Equals));
            if (artFormID > 0)
                builder.Conditions.Add(CommonSearchCondition.CreateInstance("a." + COL_EVENTS_FORM, artFormID, SearchConditionOperator.Equals));

            string whereClause = builder.ToSqlCommand();

            if (!string.IsNullOrEmpty(yearMonth) && yearMonth != "0")
            {
                EventsDateDAL dal = new EventsDateDAL(this.Language.LanguageType);
                if (date > 0)
                {
                    string sDate = (date>9) ? date.ToString() : "0" + date;
                    sDate = yearMonth + "-" + sDate;
                    whereClause += string.Format(" and a.{0} in (select {0} from {1} where {2}={3} and {4}='{5}')", COL_EVENTS_ID, dal.TableName, COL_LANG_ID, (int)Language.LanguageType, dal.Time.Column.Name, sDate);
                }
                else
                {
                    whereClause += string.Format(" and a.{0} in (select {0} from {1} where {2}={3} and substring({4},1,7)='{5}')", COL_EVENTS_ID, dal.TableName, COL_LANG_ID, (int)Language.LanguageType, dal.Time.Column.Name, yearMonth);
                }
            }

            IField[] selectedFields = new IField[] { ID, Title, SubTitle, City, ArtFormID, StartTime, EndTime, ListImg, ShowTime, HtmlFileName };

            string cmdSelect =
                string.Format(
                    "select a.{0}, a.{1}, a.{2}, a.{3}, a.{4}, a.{5}, a.{6}, a.{7}, a.{8}, a.{9}, ISNULL(b.begindate, '9999-12-31') as sorder from {10} as a {12} {11} order by sorder, {5} asc",
                    COL_EVENTS_ID, COL_EVENTS_TITLE, COL_EVENTS_SUBTITLE, COL_EVENTS_CITY, COL_EVENTS_FORM,
                    COL_EVENTS_TIME_START, COL_EVENTS_TIME_END, COL_EVENTS_LB_IMG, COL_EVENTS_TIME_SHOW,
                    COL_HTML_FILE_NAME, TableName, whereClause,
                    @"left join
                (
                select COL_EVENTS_ID,COL_LANG_ID,min(COL_EVENTS_DATE) as begindate from  dbo.UKNOW_EVENTS_DATE where COL_EVENTS_DATE>='" +
                    DateTime.Now.ToString("yyyy-MM-dd") +
                    "' group by COL_EVENTS_ID,COL_LANG_ID ) as b on a.COL_EVENTS_ID=b.COL_EVENTS_ID and a.COL_LANG_ID=b.COL_LANG_ID");
                

            pagingParams.SortingParameter = SortingParameterFactory.CreateInstance(StartTime.PropertyName, SortOrder.Asc);
            int totalRecordCount;
            DataSet dataSet = DBHelper.ExecuteDataSet(cmdSelect, pagingParams.CurrentPageIndex, pagingParams.PageSize, out totalRecordCount);
            PagingList list = new PagingList();
            list.List = GetModelListFromDataSet(ModelType, dataSet, selectedFields);
            list.Capacity = totalRecordCount;
            foreach (EventsModel model in list.List)
                SetForeignKeyModel(model);

            return list;
        }

        
        public DataTable GetAllList()
        {
            string sqlcmd = @"select a.COL_MAPPING_ID, a.COL_DATA_ID_CN,b.COL_EVENTS_TITLE,a.COL_DATA_ID_EN,c.COL_EVENTS_TITLE as COL_EVENTS_TITLE_EN from UKNOW_LANG_MAPPING  as a
                             left join UKNOW_EVENTS  as b on a.COL_DATA_ID_CN=b.COL_EVENTS_ID and b.COL_LANG_ID=2052 left join UKNOW_EVENTS 
                             as c on a.COL_DATA_ID_EN=c.COL_EVENTS_ID and c.COL_LANG_ID=1033
                             where a.COL_DATA_TYPE=1";

            DataSet ds = DBHelper.ExecuteDataSet(sqlcmd, null);

            if (ds != null)
            {
                DataTable dt = ds.Tables[0];
                return dt;
            }
            else
            {
                return null;
            }


        }

        /// <summary>
        /// 验证收藏商品的数量
        /// </summary>
        /// <param name="memberid"></param>
        /// <returns></returns>
        public bool CheckMyEvents(int memberid)
        {
            IList<FavoriteEventsModelExt> list = GetMyRecentlyEvent(memberid);
            
            //GetMyRecentlyEvent
            //IList<FavoriteEventsModel> list=GetMyFavorite(memberid,string.Empty,string.Empty);
            return list != null && list.Count > 0;

        }

        public PagingList GetMyEvents(int userID, PagingParameter pagingParams)
        {
            DataSet ds = GetMyFavoriteEventDataSet(userID, null, null);
            int total;
            DataSet dataSet = DBHelper.PagingDataSet(ds, pagingParams.CurrentPageIndex - 1, pagingParams.PageSize, out total);
            IList<FavoriteEventsModel> models = ConvertDataSetToModels(dataSet);
            PagingList list = new PagingList((IList)models, total);
            return list;
        }

        public PagingList GetMyEvents(int userID, PagingParameter pagingParams,int city)
        {
            DataSet ds = GetMyFavoriteEventDataSet(userID, null, null,city);
            int total;
            DataSet dataSet = DBHelper.PagingDataSet(ds, pagingParams.CurrentPageIndex - 1, pagingParams.PageSize, out total);
            IList<FavoriteEventsModel> models = ConvertDataSetToModels(dataSet);
            PagingList list = new PagingList((IList)models, total);
            return list;
        }

        public IList<FavoriteEventsModel> GetMyEvents(int memberid, string begintime, string endtime, bool flag)
        {
            return flag ? GetMyFavorite(memberid, DateTime.Now.ToString("yyyy-MM-dd"), endtime) : GetMyLastEvents(memberid, DateTime.Now.ToString("yyyy-MM-dd"), endtime);
            // return GetMyFavorite(memberid, begintime, endtime);

        }


        /// <summary>
        /// 取3条最近将要举办的活动
        /// </summary>
        /// <param name="memberid">用户的ID</param>
        /// <param name="begintime">开始时间</param>
        /// <param name="endtime">结束时间</param>
        /// <returns></returns>
        public IList<FavoriteEventsModel> GetMyLastEvents(int memberid, string begintime, string endtime)
        {
            var strsql =
                new StringBuilder(
                    @"select top 3  * from (
                        SELECT 
	                        b.COL_EVENTS_TITLE,
	                        b.COL_EVENTS_TIME_START,
	                        b.COL_EVENTS_VENUE,
						    b.COL_HTML_FILE_NAME,
                            b.COL_EVENTS_FORM,
	                        b.COL_EVENTS_TIME_END,
	                        d.COL_FORM_NAME,
	                        b.COL_EVENTS_LB_IMG,
	                        e.COL_REM_ALL,
	                        isnull(f.COL_EVENTS_ID,0) as eventstate,
	                        g.EventEndDate,
	                        c.cnname,
	                        c.enname,                          
                            b.COL_EVENTS_TIME_SHOW,
                            b.COL_LANG_ID,
                            0 as  IsEventExpired,
                            d.COL_FORM_CSS,
                            b.COL_EVENTS_ID 
                        from  
	                        UKNOW_EVENTS as b 
	                        left join CMS_DICTIONARY as c on c.CID=b.COL_EVENTS_CITY
	                        left join UKNOW_ART_FORM as d on b.COL_EVENTS_FORM=d.COL_FORM_ID and b.COL_LANG_ID=d.COL_LANG_ID
	                        left join UKNOW_MEMBER_REMINDER_SETTING as e on e.COL_MEMBER_ID=@memberid
	                        left join UKNOW_MEMBER_REMINDER_MAPPING as f on f.COL_LANG_ID=b.COL_LANG_ID and f.MEMBER_ID=@memberid and f.COL_EVENTS_ID=b.COL_EVENTS_ID
	                        left join (select COL_LANG_ID,COL_EVENTS_ID,MIN(COL_EVENTS_DATE) as EventEndDate from UKNOW_EVENTS_DATE  where COL_EVENTS_DATE>=@begintime group by COL_LANG_ID,COL_EVENTS_ID) as g on g.COL_EVENTS_ID=b.COL_EVENTS_ID and g.COL_LANG_ID=b.COL_LANG_ID
	                        where  b.COL_LANG_ID=@COL_LANG_ID 
                        ) as m  where m.EventEndDate>=@endtime  order by m.EventEndDate asc");

            IDataParameter[] parameters = {
					new SqlParameter("@memberid", SqlDbType.Int,4),
					new SqlParameter("@begintime", SqlDbType.NVarChar),
					new SqlParameter("@endtime", SqlDbType.NVarChar),
                    new SqlParameter("@COL_LANG_ID", SqlDbType.Int,4)
					};
            parameters[0].Value = memberid;
            parameters[1].Value = string.IsNullOrEmpty(begintime) ? DateTime.Now.ToString("yyyy-MM-dd") : begintime;
            parameters[2].Value = string.IsNullOrEmpty(endtime) ? DateTime.Now.ToString("yyyy-MM-dd") : endtime;
            parameters[3].Value = (int)this.Language.LanguageType;

            var ds = DBHelper.ExecuteDataSet(CommandType.Text, strsql.ToString(), parameters);
            if (ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
            {
                var list = ds.Tables[0].ToList<FavoriteEventsModel>();
                return list;
            }
            return null;

        }


        


        /// <summary>
        /// 根据会员提醒的活动列表
        /// </summary>
        /// <param name="memberid"></param>
        /// <returns></returns>
        public IList<FavoriteEventsModelExt>  GetMyRecentlyEvent(int memberid)
        {
            var sql = new StringBuilder();
            sql.Append(
                @"select 
					      a.COL_EVENTS_TITLE,
                          a.COL_EVENTS_TIME_START,
                          a.COL_EVENTS_FORM,
                          a.COL_EVENTS_ID,
                          a.COL_EVENTS_TIME_END,
                          a.COL_EVENTS_LB_IMG,
                          a.COL_EVENTS_TIME_SHOW,
						  a.COL_LANG_ID,
					      a.COL_EVENTS_VENUE,
					      a.COL_HTML_FILE_NAME,
					      
					      d.cnname,
					      d.enname,
					      e.COL_FORM_CSS,					      
                          e.COL_FORM_NAME,
					      0 as  IsEventExpired,
					      c.COL_REM_ALL,
					      b.EventEndDate,
						  isnull(f.COL_EVENTS_ID,0) as eventstate,
                          b.EventEndDate as COL_EVENTS_DATE					
                       from  UKNOW_EVENTS as a  
                        left join 
                        (select COL_LANG_ID,COL_EVENTS_ID,MIN(COL_EVENTS_DATE) as EventEndDate from UKNOW_EVENTS_DATE  where COL_EVENTS_DATE>=@datetimenow group by COL_LANG_ID,COL_EVENTS_ID) as b
							on a.COL_LANG_ID=b.COL_LANG_ID and b.COL_EVENTS_ID=a.COL_EVENTS_ID
						left join UKNOW_MEMBER_REMINDER_SETTING as c on c.COL_MEMBER_ID=@memberid
						left join CMS_DICTIONARY as d on d.CID=a.COL_EVENTS_CITY
						left join UKNOW_ART_FORM as e on a.COL_EVENTS_FORM=e.COL_FORM_ID and e.COL_LANG_ID=a.COL_LANG_ID
						left join UKNOW_MEMBER_REMINDER_MAPPING as f on f.COL_EVENTS_ID=a.COL_EVENTS_ID and f.COL_LANG_ID=a.COL_LANG_ID and COL_MEMBER_ID=@memberid
						where a.COL_LANG_ID=@lanagerid and b.EventEndDate is not null and (c.COL_REM_ALL=1 or  f.COL_EVENTS_ID>0) order by  b.EventEndDate asc");


            IDataParameter[] parameters = {
					new SqlParameter("@lanagerid", SqlDbType.Int,4),
					new SqlParameter("@datetimenow", SqlDbType.NVarChar,50),
					new SqlParameter("@memberid", SqlDbType.Int)
					};
            parameters[0].Value = (int)this.Language.LanguageType;
            parameters[1].Value = DateTime.Now.ToString("yyyy-MM-dd");
            parameters[2].Value = memberid;

            var ds = DBHelper.ExecuteDataSet(CommandType.Text, sql.ToString(), parameters);
            if (ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
            {
                var list = ds.Tables[0].ToList<FavoriteEventsModelExt>();
                return list;
            }
            return null;
        }

        /// <summary>
        /// My last envents （我最近的收藏的活动列表）
        /// </summary>
        /// <param name="memberid"></param>
        /// <param name="begintime"></param>
        /// <param name="endtime"></param>
        /// <returns></returns>
        private IList<FavoriteEventsModel> GetLastEvents(int memberid, string begintime, string endtime)
        {
            var strsql = new StringBuilder();
            strsql.Append(
                @"select  * from (
                        SELECT 
	                        b.COL_EVENTS_TITLE,
	                        b.COL_EVENTS_TIME_START,
	                        b.COL_EVENTS_VENUE,
									      b.COL_HTML_FILE_NAME,
                            b.COL_EVENTS_FORM,
	                        a.COL_EVENTS_ID, 
	                        b.COL_EVENTS_TIME_END,
	                        d.COL_FORM_NAME,
	                        b.COL_EVENTS_LB_IMG,
	                        e.COL_REM_ALL,
	                        isnull(f.COL_EVENTS_ID,0) as eventstate,
	                        g.EventEndDate,
	                        c.cnname,
	                        c.enname,
                            
                            b.COL_EVENTS_TIME_SHOW,
                            b.COL_LANG_ID,
                            0 as  IsEventExpired,
                            d.COL_FORM_CSS
                        from UKNOW_MEMBER_EVENTS_MAPPING as a 
	                        inner join UKNOW_EVENTS as b on a.COL_EVENTS_ID=b.COL_EVENTS_ID and a.COL_LANG_ID=b.COL_LANG_ID
	                        left join CMS_DICTIONARY as c on c.CID=b.COL_EVENTS_CITY
	                        left join UKNOW_ART_FORM as d on b.COL_EVENTS_FORM=d.COL_FORM_ID and b.COL_LANG_ID=d.COL_LANG_ID
	                        left join UKNOW_MEMBER_REMINDER_SETTING as e on e.COL_MEMBER_ID=a.MEMBER_ID
	                        left join UKNOW_MEMBER_REMINDER_MAPPING as f on f.COL_LANG_ID=a.COL_LANG_ID and f.MEMBER_ID=a.MEMBER_ID and f.COL_EVENTS_ID=b.COL_EVENTS_ID
	                        left join (select COL_LANG_ID,COL_EVENTS_ID,MIN(COL_EVENTS_DATE) as EventEndDate from UKNOW_EVENTS_DATE  where COL_EVENTS_DATE>@begintime group by COL_LANG_ID,COL_EVENTS_ID) as g on g.COL_EVENTS_ID=b.COL_EVENTS_ID and g.COL_LANG_ID=b.COL_LANG_ID
	                        where a.MEMBER_ID=@memberid and b.COL_LANG_ID=@COL_LANG_ID 
                        ) as m  where m.EventEndDate>=@begintime  order by m.EventEndDate asc");

            IDataParameter[] parameters = {
					new SqlParameter("@memberid", SqlDbType.Int,4),
					new SqlParameter("@begintime", SqlDbType.NVarChar),
					new SqlParameter("@endtime", SqlDbType.NVarChar),
                    new SqlParameter("@COL_LANG_ID", SqlDbType.Int,4)
					};
            parameters[0].Value = memberid;
            //下面的这两个暂时不用
            parameters[1].Value = string.IsNullOrEmpty(begintime) ? DateTime.Now.ToString("yyyy-MM-dd") : begintime;
            parameters[2].Value = string.IsNullOrEmpty(endtime) ? DateTime.Now.ToString("yyyy-MM-dd") : endtime;
            parameters[3].Value = (int)this.Language.LanguageType;

            var ds = DBHelper.ExecuteDataSet(CommandType.Text, strsql.ToString(), parameters);
            if (ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
            {
                var list = ds.Tables[0].ToList<FavoriteEventsModel>();
                foreach (FavoriteEventsModel model in list)
                {
                    model.IsEventExpired = EventIsExpired(model.EventEndDate, model.COL_EVENTS_TIME_END,model.COL_EVENTS_TIME_START) ? 1 : 0;
                }
                return list;
            }
            return null;

        }

        /// <summary>
        /// My Favorite events
        /// </summary>
        /// <param name="memberid"></param>
        /// <param name="begintime"></param>
        /// <param name="endtime"></param>
        /// <returns></returns>
        private IList<FavoriteEventsModel> GetMyFavorite(int memberid, string begintime, string endtime)
        {
            DataSet ds = GetMyFavoriteEventDataSet(memberid, begintime, endtime);

            return ConvertDataSetToModels(ds);

            #region 暂时不用了
            //return GetModelListFromDataSet(ModelType, ds,
            //                               new IField[]
            //                                   {
            //                                       LanguageID,ArtFormID,ShowTime,StartTime, ID, EndTime, Title, ListImg,
            //                                       FieldFactory.Create(ModelType, "cnname",
            //                                                           ColumnFactory.CreateColumn("cnname",
            //                                                                                      DataType.String,
            //                                                                                      200)),
            //                                       FieldFactory.Create(ModelType, "enname",
            //                                                           ColumnFactory.CreateColumn("enname",
            //                                                                                      DataType.String,
            //                                                                                      200))
            //                                   }).Cast<EventsModel>().ToList();
            #endregion
        }

        private IList<FavoriteEventsModel> ConvertDataSetToModels(DataSet ds)
        {
            if (ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
            {
                var list = ds.Tables[0].ToList<FavoriteEventsModel>();
                foreach (FavoriteEventsModel model in list)
                {
                    model.IsEventExpired = EventIsExpired(model.EventEndDate, model.COL_EVENTS_TIME_END,model.COL_EVENTS_TIME_START) ? 1 : 0;
                }
                return list;
            }

            return null;
        }

        private DataSet GetMyFavoriteEventDataSet(int memberid, string startTime, string endTime)
        {
            var strsql = new StringBuilder();
            strsql.Append(
                @"SELECT 
	                b.COL_LANG_ID,
	                b.COL_EVENTS_FORM,
	                b.COL_EVENTS_TIME_SHOW,
	                b.COL_EVENTS_TIME_START,                 
	                b.COL_EVENTS_ID,
	                b.COL_EVENTS_TIME_END,
	                b.COL_EVENTS_TITLE,
	                b.COL_EVENTS_LB_IMG,
                    b.COL_EVENTS_VENUE,
                    b.COL_HTML_FILE_NAME,
	                c.cnname,
	                c.enname,
                    g.eventEndDate as EventEndDate,
                    d.COL_FORM_NAME,
                    e.COL_REM_ALL,
                    isnull(f.COL_EVENTS_ID,0) as eventstate,
                    0 as IsEventExpired,
                    d.COL_FORM_CSS
                from UKNOW_MEMBER_EVENTS_MAPPING as a 
	                inner join UKNOW_EVENTS as b on a.COL_EVENTS_ID=b.COL_EVENTS_ID and a.COL_LANG_ID=b.COL_LANG_ID
	                left join CMS_DICTIONARY as c on c.CID=b.COL_EVENTS_CITY
	                left join UKNOW_ART_FORM as d on b.COL_EVENTS_FORM=d.COL_FORM_ID and b.COL_LANG_ID=d.COL_LANG_ID
	                left join UKNOW_MEMBER_REMINDER_SETTING as e on e.COL_MEMBER_ID=a.MEMBER_ID
	                left join UKNOW_MEMBER_REMINDER_MAPPING as f on f.COL_LANG_ID=a.COL_LANG_ID and f.MEMBER_ID=a.MEMBER_ID and f.COL_EVENTS_ID=b.COL_EVENTS_ID
	                left join (select COL_LANG_ID,COL_EVENTS_ID,MIN(COL_EVENTS_DATE) as EventEndDate from UKNOW_EVENTS_DATE  where COL_EVENTS_DATE>=@begintime group by COL_LANG_ID,COL_EVENTS_ID) as g on g.COL_EVENTS_ID=b.COL_EVENTS_ID
	                and g.COL_LANG_ID=a.COL_LANG_ID
                where a.MEMBER_ID=@memberid and b.COL_LANG_ID=@COL_LANG_ID");
            IDataParameter[] parameters = {
					new SqlParameter("@memberid", SqlDbType.Int,4),
                    new SqlParameter("@begintime", SqlDbType.NVarChar),
                    //new SqlParameter("@endtime", SqlDbType.DateTime),
                    new SqlParameter("@COL_LANG_ID", SqlDbType.Int,4)
					};
            parameters[0].Value = memberid;
            //下面的这两个暂时不用
            parameters[1].Value = string.IsNullOrEmpty(startTime) ? DateTime.Now.ToString("yyyy - MM - dd") : startTime;
            //parameters[2].Value = string.IsNullOrEmpty(endtime) ? DateTime.Now.ToString() : endtime;
            parameters[2].Value = (int)this.Language.LanguageType;
            var ds = DBHelper.ExecuteDataSet(CommandType.Text, strsql.ToString(), parameters);
            return ds;
        }

        private DataSet GetMyFavoriteEventDataSet(int memberid, string startTime, string endTime,int city)
        {
            var swhere = " and 1=1";
            if(city>0)
            {
                swhere = " and COL_EVENTS_CITY=" + city;
            }
            var strsql = new StringBuilder();
            strsql.Append(
                @"SELECT 
	                b.COL_LANG_ID,
	                b.COL_EVENTS_FORM,
	                b.COL_EVENTS_TIME_SHOW,
	                b.COL_EVENTS_TIME_START,                 
	                b.COL_EVENTS_ID,
	                b.COL_EVENTS_TIME_END,
	                b.COL_EVENTS_TITLE,
	                b.COL_EVENTS_LB_IMG,
                    b.COL_EVENTS_VENUE,
                    b.COL_HTML_FILE_NAME,
	                c.cnname,
	                c.enname,
                    g.eventEndDate as EventEndDate,
                    d.COL_FORM_NAME,
                    e.COL_REM_ALL,
                    isnull(f.COL_EVENTS_ID,0) as eventstate,
                    0 as IsEventExpired,
                    d.COL_FORM_CSS
                from UKNOW_MEMBER_EVENTS_MAPPING as a 
	                inner join UKNOW_EVENTS as b on a.COL_EVENTS_ID=b.COL_EVENTS_ID and a.COL_LANG_ID=b.COL_LANG_ID
	                left join CMS_DICTIONARY as c on c.CID=b.COL_EVENTS_CITY
	                left join UKNOW_ART_FORM as d on b.COL_EVENTS_FORM=d.COL_FORM_ID and b.COL_LANG_ID=d.COL_LANG_ID
	                left join UKNOW_MEMBER_REMINDER_SETTING as e on e.COL_MEMBER_ID=a.MEMBER_ID
	                left join UKNOW_MEMBER_REMINDER_MAPPING as f on f.COL_LANG_ID=a.COL_LANG_ID and f.MEMBER_ID=a.MEMBER_ID and f.COL_EVENTS_ID=b.COL_EVENTS_ID
	                left join (select COL_LANG_ID,COL_EVENTS_ID,MIN(COL_EVENTS_DATE) as EventEndDate from UKNOW_EVENTS_DATE  where COL_EVENTS_DATE>=@begintime group by COL_LANG_ID,COL_EVENTS_ID) as g on g.COL_EVENTS_ID=b.COL_EVENTS_ID
	                and g.COL_LANG_ID=a.COL_LANG_ID
                where a.MEMBER_ID=@memberid and b.COL_LANG_ID=@COL_LANG_ID " + swhere + " order by a.memberdate desc ");
            IDataParameter[] parameters = {
					new SqlParameter("@memberid", SqlDbType.Int,4),
                    new SqlParameter("@begintime", SqlDbType.NVarChar),
                    //new SqlParameter("@endtime", SqlDbType.DateTime),
                    new SqlParameter("@COL_LANG_ID", SqlDbType.Int,4)
					};
            parameters[0].Value = memberid;
            //下面的这两个暂时不用
            parameters[1].Value = string.IsNullOrEmpty(startTime) ? DateTime.Now.ToString("yyyy - MM - dd") : startTime;
            //parameters[2].Value = string.IsNullOrEmpty(endtime) ? DateTime.Now.ToString() : endtime;
            parameters[2].Value = (int)this.Language.LanguageType;
            var ds = DBHelper.ExecuteDataSet(CommandType.Text, strsql.ToString(), parameters);
            return ds;
        }


        /// <summary>
        /// 判断活动是否过期（没有测试)
        /// </summary>
        /// <param name="eventdate">活动的结束日期</param>
        /// <param name="endtime">Event 表结束日期</param>
        /// <param name="begintime"> </param>
        /// <returns>true 过期 false 不过期</returns>
        private bool EventIsExpired(string eventdate, DateTime? endtime,DateTime? begintime)
        {
            if (string.IsNullOrEmpty(eventdate))
                return true;
            else
            {
                if (!endtime.HasValue&&!begintime.HasValue)
                {
                    return true;
                }
                
                var now = DateTime.Now.ToString("yyyy-MM-dd");
                DateTime eDateTime;              
                if (DateTime.TryParse(eventdate, out eDateTime))
                {
                    if (System.String.CompareOrdinal(now, eDateTime.ToString("yyyy-MM-dd")) > 0)
                    {
                        return true;
                    }
                }

            }
            return false;
        }

      
      /// <summary>
      /// 用户是否赞过
      /// </summary>
      /// <param name="memberid"></param>
      /// <param name="eventid"></param>
      /// <returns></returns>
      public  bool CheckUserUp(int memberid, int eventid)
      {
          IDataParameter[] parameters = {
					new SqlParameter("@meberid", SqlDbType.Int,4),
                    new SqlParameter("@eventid", SqlDbType.Int,4)
					};
          parameters[0].Value = memberid;
          parameters[1].Value = eventid;

          using (IDataReader reader = DBHelper.ExecuteReader("Select count(1) from UKNOW_MEMBER_PraiseEVENTS where MEMBER_ID=@meberid and COL_EVENTS_ID=@eventid",parameters))
          {
              while (reader.Read())
              {
                  return Convert.ToInt32(reader[0]) > 0;
                 
              }
          }
          return false;
      }

       /// <summary>
       /// 验证一个活动是否在举办
       /// </summary>
       /// <param name="eventid"></param>
       /// <returns></returns>
      public bool CheckEventBegin(int eventid)
       {
           var sql =
               new StringBuilder(
                   @"select COUNT(COL_EVENTS_ID)  from dbo.UKNOW_EVENTS_DATE 
                where COL_LANG_ID=@langerid and COL_EVENTS_ID=@eventid and COL_EVENTS_DATE>=@begindate");

           IDataParameter[] parameters = {
					new SqlParameter("@langerid", SqlDbType.Int,4),
                    new SqlParameter("@eventid", SqlDbType.Int,4),
                    new SqlParameter("@begindate", SqlDbType.NVarChar,50)
					};
           parameters[0].Value = (int) this.Language.LanguageType;
           parameters[1].Value = eventid;
           parameters[2].Value = DateTime.Now.ToString("yyyy-MM-dd");
           using (IDataReader reader = DBHelper.ExecuteReader(sql.ToString(), parameters))
           {
               while (reader.Read())
               {
                   return Convert.ToInt32(reader[0]) > 0;

               }
           }
           return false;

       }
      
 
      /// <summary>
      /// 根据用户ID和活动ID查询 是否收藏过活动 >0 收藏
      /// </summary>
      /// <param name="memberid"></param>
      /// <param name="eventid"></param>
      /// <returns></returns>
      public int CheckUserFavEvent(int memberid,int eventid)
      {
          var sql =
              new StringBuilder(
                  @"select COUNT(1) from 
                        UKNOW_MEMBER_EVENTS_MAPPING as a 
                        inner join UKNOW_EVENTS as b 
                        on a.COL_EVENTS_ID=b.COL_EVENTS_ID and a.COL_LANG_ID=b.COL_LANG_ID 
                        inner join UKNOW_MEMBER_INFO as c on c.MEMBER_ID=a.MEMBER_ID
                        where a.MEMBER_ID=@meberid and a.COL_LANG_ID=@lanagerid and a.COL_EVENTS_ID=@eventid");

          IDataParameter[] parameters = {
					new SqlParameter("@meberid", SqlDbType.Int,4),
                     new SqlParameter("@lanagerid", SqlDbType.Int,4),
                    new SqlParameter("@eventid", SqlDbType.Int,4)
					};
          parameters[0].Value = memberid;
          parameters[1].Value = (int)this.Language.LanguageType;
          parameters[2].Value = eventid;

          using (IDataReader reader = DBHelper.ExecuteReader(sql.ToString(), parameters))
          {
              while (reader.Read())
              {
                  return Convert.ToInt32(reader[0]);

              }
          }
          return 0;

      }
      
      public DataSet  GDataSet (string sql)
      {
          return DBHelper.ExecuteDataSet(CommandType.Text, sql);
      }
      
      /// <summary>
      /// 取活动的最近举办日期
      /// </summary>
      /// <returns></returns>
      public IList<EventBeginDate>  GBeginDates ()
      {
          var sql =
              new StringBuilder(
                  "select COL_EVENTS_ID as Eventid,MIN(Col_Events_Date) as Beingdate from dbo.UKNOW_EVENTS_DATE where COL_LANG_ID=@lanagerid and Col_Events_Date>=@begindate group by COL_EVENTS_ID order by MIN(Col_Events_Date) asc");
          IDataParameter[] parameters = {
					new SqlParameter("@lanagerid", SqlDbType.Int,4),
                    new SqlParameter("@begindate", SqlDbType.NVarChar),                 
					};
          parameters[0].Value = (int)this.Language.LanguageType;
          parameters[1].Value = DateTime.Now.ToString("yyyy-MM-dd");
          var ds = DBHelper.ExecuteDataSet(CommandType.Text, sql.ToString(), parameters);
          if(ds.Tables.Count>0&&ds.Tables[0].Rows.Count>0)
          {
              return ds.Tables[0].ToList<EventBeginDate>();
          }
          return null;
      }

      #region 新加的用于手机接口 2012-5-3
      public PagingList GetEventsByCityIDPhoneInterface(int CityID, PagingParameter pagingParams)
      {

          return GetFilteredEventsModelsPhoneInterface(CityID, -1, "0", -1, pagingParams);
      }
      public PagingList GetEventsByCityIDAndArtIDPhoneInterface(int CityID, int artID, PagingParameter pagingParams)
      {
          return GetFilteredEventsModelsPhoneInterface(CityID, artID, "0", -1, pagingParams);
      }
      public PagingList GetEventsByCityAndDatePhoneInterface(int CityID, string yymm, PagingParameter pagingParams)
      {
          if (yymm.Length == 10)
          {
              int date = Convert.ToDateTime(yymm).Date.Day;
              yymm = yymm.Substring(0, 7);
              return GetFilteredEventsModelsPhoneInterface(CityID, -1, yymm, date, pagingParams);
          }
          else
          {
              return GetFilteredEventsModelsPhoneInterface(CityID, -1, yymm, -1, pagingParams);
          }
      }

      /// <summary>
      /// 根据城市ID，艺术形式，年月日获取活动(用于手机接口) 2012-5-3 李学明
      /// </summary>
      /// <param name="cityID">城市ID</param>
      /// <param name="artFormID">艺术形式ID</param>
      /// <param name="yearMonth">年份</param>
      /// <param name="date">日期</param>
      /// <param name="pagingParams">分页参数</param>
      /// <returns></returns>
      public PagingList GetFilteredEventsModelsPhoneInterface(int cityID, int artFormID, string yearMonth, int date, PagingParameter pagingParams)
      {
          WhereClauseBuilder builder = WhereClauseBuilder.CreateInstance(SearchConditionJoinOperator.AND);
          builder.Conditions.Add(CommonSearchCondition.CreateInstance(COL_LANG_ID, (int)this.Language.LanguageType, SearchConditionOperator.Equals));
          builder.Conditions.Add(CommonSearchCondition.CreateInstance(COL_EVENTS_STATE, 1, SearchConditionOperator.Equals));

          if (cityID > 0)
              builder.Conditions.Add(CommonSearchCondition.CreateInstance(COL_EVENTS_CITY, cityID, SearchConditionOperator.Equals));
          if (artFormID > 0)
              builder.Conditions.Add(CommonSearchCondition.CreateInstance(COL_EVENTS_FORM, artFormID, SearchConditionOperator.Equals));

          string whereClause = builder.ToSqlCommand();

          if (!string.IsNullOrEmpty(yearMonth) && yearMonth != "0")
          {
              EventsDateDAL dal = new EventsDateDAL(this.Language.LanguageType);
              if (date > 0)
              {
                  string sDate = (date > 9) ? date.ToString() : "0" + date;
                  sDate = yearMonth + "-" + sDate;
                  whereClause += string.Format(" and {0} in (select {0} from {1} where {2}={3} and {4}='{5}')", COL_EVENTS_ID, dal.TableName, COL_LANG_ID, (int)Language.LanguageType, dal.Time.Column.Name, sDate);
              }
              else
              {
                  whereClause += string.Format(" and {0} in (select {0} from {1} where {2}={3} and substring({4},1,7)='{5}')", COL_EVENTS_ID, dal.TableName, COL_LANG_ID, (int)Language.LanguageType, dal.Time.Column.Name, yearMonth);
              }
          }
          // LanguageID, ID, State, Title, SubTitle, SponsorID, ArtFormID, City, Venue, StartTime, EndTime, ShortDescription, PhoneDetail, Detail, Education, IsHot, IsRecommendation, Order, IsHomepageTop, IsColumnTop, ColumnID, LayerIDs, InsertedTime, UpdatedTime, ListImg, TopImg, TemplateID, TicketUrl, VenueImagePath, IsSeriesMain, SeriesMainEventsID, IsSpecial, Lat, Lng, Week, ShowTime, UpLoadHtmlName, AllowUploadAttachToComment, SEOMetaDescription, SEOMetaKeywords, SEOTitle, IsHtmlFile, HtmlFileName, PhoneTitle 

          IField[] selectedFields = new IField[] { ID, Title, SubTitle, City, ArtFormID, StartTime, EndTime, ListImg, ShowTime, HtmlFileName, PhoneTitle, PhoneDetail, Venue, TopImg, TicketUrl, VenueImagePath, PhoneVenue, Lng,Lat };

          string cmdSelect = string.Format("select {0}, {1}, {2}, {3}, {4}, {5}, {6}, {7}, {8}, {9}, ISNULL({5}, '9999-12-31') as sorder,isnull(Beingdate,'2020-12-30') as Beingdate,{12},{13},{14},{15},{16},{17},{18},{19},{20} from {10}   left join  (select COL_EVENTS_ID as eventid ,MIN(Col_Events_Date) as Beingdate from dbo.UKNOW_EVENTS_DATE where COL_LANG_ID=2052 and Col_Events_Date>=CONVERT(varchar(10),GETDATE(),120) group by COL_EVENTS_ID) as b  on COL_EVENTS_ID=b.eventid   {11} order by  Beingdate, {5} asc",
              COL_EVENTS_ID, COL_EVENTS_TITLE, COL_EVENTS_SUBTITLE, COL_EVENTS_CITY, COL_EVENTS_FORM, COL_EVENTS_TIME_START, COL_EVENTS_TIME_END, COL_EVENTS_LB_IMG, COL_EVENTS_TIME_SHOW, COL_HTML_FILE_NAME, TableName, whereClause, COL_EVNETS_PHONE_TITLE, COL_EVENTS_PHONE_DETAIL, COL_EVENTS_VENUE, COL_EVENTS_TOP_IMG, COL_EVENTS_TICKET_URL, COL_EVENTS_VENUE_IMG, COL_EVENTS_PHONE_VENUE, COL_EVENTS_LONGITUDE, COL_EVENTS_LATITUDE);

          //pagingParams.SortingParameter = SortingParameterFactory.CreateInstance("Beingdate", SortOrder.Asc);
          pagingParams.SortingParameter = null;
          int totalRecordCount;
          DataSet dataSet = DBHelper.ExecuteDataSet(cmdSelect, pagingParams.CurrentPageIndex, pagingParams.PageSize, out totalRecordCount);
          PagingList list = new PagingList();
          list.List = GetModelListFromDataSet(ModelType, dataSet, selectedFields);
          list.Capacity = totalRecordCount;
          foreach (EventsModel model in list.List)
              SetForeignKeyModel(model);

          return list;
      }
      #endregion

        /// <summary>
        /// 赞的功能取消了，所有用户赞的数据也就不需要了 2012-5-10 李学明
        /// </summary>
        /// <param name="pagingParams"></param>
        /// <param name="iTotalPage"></param>
        /// <param name="iTotalRow"></param>
        /// <returns></returns>
        public IList<UserEventList>  GetUserEventlist(PagingParameter pagingParams,out int iTotalPage,out int iTotalRow)
        {
            var sql =
                new StringBuilder(
                    @"(select m.*,n.COL_EVNETS_PHONE_TITLE as Title from (
select a.member_name as UserName,a.member_id as UserID,b.COL_COMMENT_TEXT as Content,b.COL_COMMENT_IMG_NAME as Commentimg,b.COL_COMMENT_TIME as Showtime,b.COL_EVENTS_ID as Eventid,a.MEMBER_HERD_IMG as avatar from dbo.UKNOW_MEMBER_INFO as a 
inner join UKNOW_EVENTS_COMMENT as b  on a.MEMBER_ID=b.COL_MEMBER_ID and b.COL_LANG_ID=2052
union 
select a.member_name as UserName,a.member_id as UserID,'收藏' as Content,''  as Commentimg,isnull(b.memberdate,'2010-1-1') as Showtime,B.COL_EVENTS_ID,a.MEMBER_HERD_IMG as avatar from dbo.UKNOW_MEMBER_INFO as a
inner join UKNOW_MEMBER_EVENTS_MAPPING as b on a.MEMBER_ID=b.MEMBER_ID and b.COL_LANG_ID=2052

union
select a.member_name as UserName,a.member_id as UserID,'签到' as Content,''  as Commentimg,b.COL_CHECKIN_DATE as Showtime,b.COL_EVENTS_ID,a.MEMBER_HERD_IMG as avatar from dbo.UKNOW_MEMBER_INFO as a
inner join UKNOW_EVENTS_CHECKIN as b on a.MEMBER_ID=b.MEMBER_ID and b.COL_LANG_ID=2052 
) as m inner join UKNOW_EVENTS as n on m.Eventid=n.COL_EVENTS_ID and n.COL_LANG_ID=2052 and n.COL_EVENTS_STATE=1
) as v");

//            union
//select a.member_name as UserName,a.member_id as UserID,'赞' as Content,''  as Commentimg,b.COL_PraiseDate as Showtime,b.COL_EVENTS_ID,a.MEMBER_HERD_IMG as avatar from dbo.UKNOW_MEMBER_INFO as a
//inner join dbo.UKNOW_MEMBER_PraiseEVENTS as b on a.MEMBER_ID=b.MEMBER_ID and b.COL_LANG_ID=2052

            IDataParameter[] parameters = {
					new SqlParameter("@TableName", SqlDbType.NVarChar, 4000),
					new SqlParameter("@Fields", SqlDbType.NVarChar, 4000),
					new SqlParameter("@OrderField", SqlDbType.NVarChar,4000),
					new SqlParameter("@sqlWhere", SqlDbType.NVarChar,4000),
					new SqlParameter("@pageSize", SqlDbType.Int),
					new SqlParameter("@pageIndex", SqlDbType.Int),
					new SqlParameter("@TotalPage", SqlDbType.Int),
					new SqlParameter("@TotalRow", SqlDbType.Int),
					};
            parameters[0].Value = sql.ToString();
            parameters[1].Value = "*";
            parameters[2].Value = "Showtime desc";
            parameters[3].Value = "";
            parameters[4].Value = pagingParams.PageSize;
            parameters[5].Value = pagingParams.CurrentPageIndex;
            parameters[6].Direction = ParameterDirection.Output;
            parameters[7].Direction = ParameterDirection.ReturnValue;

            DataSet ds = DBHelper.ExecuteDataSet(CommandType.StoredProcedure, "SP_GetRecordByPage", parameters);//"SP_GetRecordByPage", parameters);
            iTotalPage = Convert.ToInt32(parameters[6].Value);
            iTotalRow = Convert.ToInt32(parameters[7].Value);

            if(ds.Tables.Count>0&&ds.Tables[0].Rows.Count>0)
            {
                return ds.Tables[0].ToList<UserEventList>();
            }
            return null;

        }


        public static DataTable GetList(string sTableName, string sFields, string sOrderField, string sWhere, int iPageSize, int iPageIndex, out int iTotalPage, out int iTotalRow)
        {
            IDataParameter[] parameters = {
					new SqlParameter("@TableName", SqlDbType.NVarChar, 4000),
					new SqlParameter("@Fields", SqlDbType.NVarChar, 4000),
					new SqlParameter("@OrderField", SqlDbType.NVarChar,4000),
					new SqlParameter("@sqlWhere", SqlDbType.NVarChar,4000),
					new SqlParameter("@pageSize", SqlDbType.Int),
					new SqlParameter("@pageIndex", SqlDbType.Int),
					new SqlParameter("@TotalPage", SqlDbType.Int),
					new SqlParameter("@TotalRow", SqlDbType.Int),
					};
            parameters[0].Value = sTableName;
            parameters[1].Value = sFields;
            parameters[2].Value = sOrderField;
            parameters[3].Value = sWhere;
            parameters[4].Value = iPageSize;
            parameters[5].Value = iPageIndex;
            parameters[6].Direction = ParameterDirection.Output;
            parameters[7].Direction = ParameterDirection.ReturnValue;

            const string sql = @"Declare @sql nvarchar(4000);
                Declare @totalRecord int;    
                if (@SqlWhere='' or @sqlWhere is NULL)
                    set @sql = 'select @totalRecord = count(*) from ' + @TableName
                else
                    set @sql = 'select @totalRecord = count(*) from ' + @TableName + ' where ' + @sqlWhere
                print @sql
                EXEC sp_executesql @sql,N'@totalRecord int OUTPUT',@totalRecord OUTPUT       
                select @TotalPage=CEILING((@totalRecord+0.0)/@PageSize)
                if (@SqlWhere='' or @sqlWhere is NULL)
                    set @sql = 'Select * FROM (select ROW_NUMBER() Over(order by ' + @OrderField + ') as rowId,' + @Fields + ' from ' + @TableName 
                else
                    set @sql = 'Select * FROM (select ROW_NUMBER() Over(order by ' + @OrderField + ') as rowId,' + @Fields + ' from ' + @TableName + ' where ' + @SqlWhere    
                if @PageIndex<=0 
                    Set @pageIndex = 1
                if @pageIndex>@TotalPage
                    Set @pageIndex = @TotalPage
                Declare @StartRecord int
                Declare @EndRecord int
                set @StartRecord = (@pageIndex-1)*@PageSize + 1
                set @EndRecord = @StartRecord + @pageSize - 1
                set @Sql = @Sql + ') as t where rowId between ' + Convert(varchar(50),@StartRecord) + ' and ' +  Convert(varchar(50),@EndRecord)+' order by rowId asc '
                print @sql
                Exec(@Sql)";

            DataSet ds = DBHelper.ExecuteDataSet(CommandType.Text, sql, parameters);
            //DataSet ds = SqlHelper.RunProcedure("SP_GetRecordByPage", parameters, "ds");
            iTotalPage = Convert.ToInt32(parameters[6].Value);
            iTotalRow = Convert.ToInt32(parameters[7].Value);
            return ds.Tables[0];
        }

        #endregion
    }
}
